SQL SERVER一次统计脚本

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;