Delphi按月份统计

用Delphi开发的数据库应用软件，按月份统计生成报表，是很常见的需求。但如果要求按产品排列，以月份作为栏来进行小计，还是需要一点技巧。以下是Delphi + SqlServer开发环境下的代码。

取出数据存入临时表 tmpDb，把销售日期转换成YYYYMM的年月格式。

procedure TfmaxSaleTrend.btnEnqFGClick(Sender: TObject);
var
  tmpDb,tmpEnq:String;
begin
  inherited;
  screen.Cursor:=crSqlWait;
  tmpDb:='#jhProd_x53_1'+sys_usid;
  sSql:='if object_id(N''[Tempdb]..['+tmpDb+']'') is not null  Drop table '+tmpDb;
  q01.Close;
  q01.SQL.Text:=sSql;
  q01.ExecSQL;
  sDay1:=FormatDatetime('YYYYMMDD',dtpFrom.Date);
  sDay2:=FormatDatetime('YYYYMMDD',dtpTo.Date);
  begin
    sSql:='Select ItemId,OtsYM,sum(SalesQty) as SalesQty into '+tmpDb
      +' from ('
      +' select ItemId,Year(ConfirmedDlv)*100+Month(ConfirmedDlv)as OtsYM,SalesQty '
      +' from SalesLine d1 '
      +' where SalesStatus < 4 '
      +' and ConfirmedDlv >='+''''+sDay1+''''+' and ConfirmedDlv <='+''''+sDay2+''''
      +' and exists(select * from SalesTable d2 where d1.SalesId=d2.SalesId '
      +' and d2.SalesPoolId in (''GEN'',''SPO'',''C2C'',''SPC'',''CN'')) '
      +' and exists(select * from InventTable d3 where d1.ItemId=d3.ItemId '
      +' and substring(d3.ItemGroupId,1,3)=''PD-'')'
      +' ) as dA group by ItemId,OtsYM ';
  end;
  q01.Close;
  q01.SQL.Text:=sSql;
  q01.ExecSQL;

把上述临时表中的数据，按月份展开再存入第二个临时表 tmpEnq

  tmpEnq:='#jhProd_x53_2'+sys_usid;
  sSql:='if object_id(N''[Tempdb]..['+tmpEnq+']'') is not null  Drop table '+tmpEnq;
  q01.Close;
  q01.SQL.Text:=sSql;
  q01.ExecSQL;
  sSql:='Select d1.ItemId,sum(d1.SalesQty) as SalesSum';
  qCalc.Close;
  qCalc.SQL.Text:='select distinct OtsYM from '+tmpDb+' order by OtsYM';
  qCalc.Open;
  while not qCalc.Eof do
  begin
    sField:=qCalc.FieldByName('OtsYM').AsString;
    sSql:=sSql+',(select SalesQty from '+tmpDb+' d2 where d2.ItemId=d1.ItemId '
            +'and d2.OtsYM='+''''+sField+''''+') as '+''''+sField+'''';
    qCalc.Next;
  end;
  sSql:=sSql+' into '+tmpEnq+' from '+tmpDb+' d1 group by d1.ItemId';
  q01.Close;
  q01.SQL.Text:=sSql;
  q01.ExecSQL;

把第二个临时表 tmpEnq的数据与基础表中的数据，关联后取出来显示

  qProds.Close;
  if rdgType.ItemIndex = 0 then
  begin
    qProds.SQL.Text:='select d2.CustModelNo,d1.* from '
                  +tmpEnq+' d1,InventTable d2 where d1.ItemId=d2.ItemId';
  end else
  begin
    qProds.SQL.Text:='select * from '+tmpEnq;
  end;
  qProds.Open;
  qProds.Sort:=sField+' DESC';
  dbGridEh1.Columns[0].Footer.ValueType:=fvtStaticText;
  dbGridEh1.Columns[0].Footer.Value:='合計';
  screen.Cursor:=crDefault;

上述案例是在Delphi中实现的，在程序中执行，如果改到SQL Server里，比例写成视频或预存存储过程，执行时套入变量，就更加的灵活。见下述 SQL 语句：

declare @param1 varchar(10)
declare @param2 varchar(10)
declare @param3 varchar(10)
set @param1='2018-07-01'
set @param2='2018-07-15'
set @param3='fin'

if object_id(N'[tempdb]..[#_jhERPsoDaily]') is not null drop table [#_jhERPsoDaily]

select d1.itID,d2.TransDate,sum(d1.Qty)as Qty,sum(d1.LineAmount)as Amount
  into [#_jhERPsoDaily]
  from soSaleJLine d1,soSaleJTable d2
  where d1.JournalID=d2.JournalID
  and d2.TransDate>=@param1 and d2.TransDate<=@param2
  and d2.Posted=1 and d2.CompName=@param3
  group by d1.itID,d2.TransDate

declare @i       Integer
declare @j       Integer
declare @day	 smalldatetime
declare @strDay  varchar(10)
declare @sql     varchar(8000)

set @j=DateDiff(day,@param1,@param2)
set @day=@param1
set @i=0

if(@j > 31)
begin
  set @sql='select ''日期范围不能超过30天'' as 警告 '
end
else
begin
set @sql='Select d1.itID,d2.itName,d2.stUnit,sum(d1.qty)as 总数量,sum(d1.Amount) as AllAmount '
while (@i<=@j)
begin
 set @strDay=convert(varchar(10),@day,112)
 set @sql=@sql+',(select qty from [#_jhERPsoDaily] d8 where d8.itID=d1.itID '
 set @sql=@sql+'and d8.TransDate='+''''+@strDay+''''+') as '+''''+@strDay+''+'数量'' '
 set @sql=@sql+',(select Amount from [#_jhERPsoDaily] d8 where d8.itID=d1.itID '
 set @sql=@sql+'and d8.TransDate='+''''+@strDay+''''+') as '+''''+@strDay+''+'金额'' '
 set @i=@i+1
 set @day=DateAdd(day,1,@day)
end
set @sql=@sql+'from [#_jhERPsoDaily] d1 left join enInvent d2 on d1.itID=d2.itID '
set @sql=@sql+'group by d1.itID,d2.itName,d2.stUnit'
end
exec(@sql)