DECLARE @x varchar(7),@num varchar(35),@num2 varchar(35),@num3 varchar(35),@num4 varchar(35), @num5 int,@num6 int,@num7 int,@num8 int; BEGIN ---------------------------------------Electricity Meter--------------------------------------------------------- --Electricity Meter quantity set @num5 = (SELECT COUNT(1) FROM DA_BJ); print('Electricity Meter quantity:'+cast(@num5 as varchar)); --Purchased Electricity Meter quantity set @num6 = (SELECT COUNT(DISTINCT D.BJJH) FROM DA_BJ D, ORDER_MASTER M WHERE D.BJJH = M.METERNO); print('Purchased Electricity Meter quantity:'+cast(@num6 as varchar)); print(''); --title print(right(replicate(' ',7)+ltrim('Date'),7) + '|' + right(replicate(' ',35)+ltrim('Ele Meter purchase token Num'),35) + '|' + right(replicate(' ',35)+ltrim('Ele Meter management token Num'),35) + '|'); print('--------------------------------------------------------------------------------'); --loop begin set @x = '2015-12'; WHILE @x < FORMAT(getdate(), 'yyyy-MM') BEGIN set @x = FORMAT(dateadd(month,1,convert(date,@x+'-01')), 'yyyy-MM'); --Number of Electricity Meter purchase token set @num = ( SELECT right(replicate(' ',35)+ltrim(cast(COUNT(t.token) as varchar)),35) FROM ORDER_TOKEN T, ORDER_MASTER M WHERE T.ORDERSID = M.ORDERSID AND M.ORDER_TYPE IN ('01','23') AND M.ORDER_TYPE NOT IN ('15','16') AND M.RES_TYPE = '03' and FORMAT(M.op_time, 'yyyy-MM') = @x); --Number of Electricity Meter management token set @num2 = ( SELECT right(replicate(' ',35)+ltrim(cast(COUNT(t.token) as varchar)),35) FROM ORDER_TOKEN T, ORDER_MASTER M WHERE T.ORDERSID = M.ORDERSID AND M.ORDER_TYPE NOT IN ('01','23','15','16') AND M.RES_TYPE = '03' and FORMAT(M.op_time, 'yyyy-MM') = @x); print(right(replicate(' ',7)+ltrim(@x),7) + '|' + @num + '|' + @num2 + '|'); print('--------------------------------------------------------------------------------'); END; --loop END print(''); ---------------------------------------Water Meter--------------------------------------------------------- --Water Meter quantity set @num7 = (SELECT COUNT(1) FROM DA_SB); print('Water Meter quantity:'+cast(@num7 as varchar)); --Purchased Water Meter quantity set @num8 = (SELECT COUNT(DISTINCT D.SBJH) FROM DA_SB D, ORDER_MASTER M WHERE D.SBJH = M.METERNO); print('Purchased Water Meter quantity:'+cast(@num8 as varchar)); print(''); --title print(right(replicate(' ',7)+ltrim('Date'),7) + '|' + right(replicate(' ',35)+ltrim('Water Meter purchase token Num'),35) + '|' + right(replicate(' ',35)+ltrim('Water Meter management token Num'),35) + '|'); print('-------------------------------------------------------------------------------'); --loop begin set @x = '2015-12'; WHILE @x < FORMAT(getdate(), 'yyyy-MM') BEGIN set @x = FORMAT(dateadd(month,1,convert(date,@x+'-01')), 'yyyy-MM'); --Number of Water Meter purchase token set @num3 = ( SELECT right(replicate(' ',35)+ltrim(cast(COUNT(t.token) as varchar)),35) FROM ORDER_TOKEN T, ORDER_MASTER M WHERE T.ORDERSID = M.ORDERSID AND M.ORDER_TYPE IN ('01','23') AND M.ORDER_TYPE NOT IN ('15','16') AND M.RES_TYPE = '02' and FORMAT(M.op_time, 'yyyy-MM') = @x); --Number of Water Meter management token set @num4 = ( SELECT right(replicate(' ',35)+ltrim(cast(COUNT(t.token) as varchar)),35) FROM ORDER_TOKEN T, ORDER_MASTER M WHERE T.ORDERSID = M.ORDERSID AND M.ORDER_TYPE NOT IN ('01','23','15','16') AND M.RES_TYPE = '02' and FORMAT(M.op_time, 'yyyy-MM') = @x); print(right(replicate(' ',7)+ltrim(@x),7) + '|' + @num3 + '|' + @num4 + '|'); print('--------------------------------------------------------------------------------'); END; --loop END END;