SQLStr := 'select Qc.CoutName,Qc.QcYingSK,Qc.QcYiSK,Bq.BqYingSK,Bq.BqYiSK,'+
'Qc.QcYingSK+Bq.BqYingSK-Qc.QcYiSK-Bq.BqYiSK as QmYingSK from '+ ' (select c.CoutName,sum(a.TNumber*a.Price) as QcYingSK,'+
' sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as QcYiSK '+
' from (T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode) '+
' left join T_Bill c on a.BillCode=c.BillCode '+
' where c.addDate < :StartDate '; if cbbBillType.Text ='销售发货' then SQLStr := SQLStr + ' and c.Identifier=''销售发货'' ';
if cbbBillType.Text ='销售退货' then SQLStr := SQLStr + ' and c.Identifier=''销售退货'' ';
if cbbBillType.Text ='发货退货' then SQLStr := SQLStr + ' and (c.Identifier=''销售发货'' or c.Identifier=''销售退货'') ' ; if cbbDepot.Text <>'' then SQLStr := SQLStr +
' and c.Depot like '+ Quotedstr('%'+cbbDepot.Text+'%'); if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%'); if cbbBillUser.Text <>'' then SQLStr := SQLStr +
' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%'); SQLStr := SQLStr +' group by c.CoutName) Qc'; SQLStr := SQLStr +' full outer join on Qc.CoutName=Bq.CoutName '+ ' (select c.CoutName,sum(a.TNumber*a.Price) as BqYingSK,'+
' sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as BqYiSK '+
' from (T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode) '+
' left join T_Bill c on a.BillCode=c.BillCode '+
' where c.addDate between :StartDate and :EndDate '; if cbbBillType.Text ='销售发货' then SQLStr := SQLStr + ' and c.Identifier=''销售发货'' ';
if cbbBillType.Text ='销售退货' then SQLStr := SQLStr + ' and c.Identifier=''销售退货'' ';
if cbbBillType.Text ='发货退货' then SQLStr := SQLStr + ' and (c.Identifier=''销售发货'' or c.Identifier=''销售退货'') ' ; if cbbDepot.Text <>'' then SQLStr := SQLStr +
' and c.Depot like '+ Quotedstr('%'+cbbDepot.Text+'%'); if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%'); if cbbBillUser.Text <>'' then SQLStr := SQLStr +
' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%'); SQLStr := SQLStr +' group by c.CoutName) Bq '; with qrySkYsZkHzCout do
begin
Close;
SQL.Clear;
SQL.Add(SQLStr);
Parameters.ParamByName('StartDate').Value:=edtBeginCout.Value;
Parameters.ParamByName('EndDate').Value:=edtEndCout.Value;
Open;
end;
这些代码需要在Access上查询,查询出来就效果就是:
客户|期初应收款|期初已收款|本期应收款|本期已收款|期末未收款
里面三个表的关系,请查看http://topic.csdn.net/u/20110904/13/7427b16f-0e7d-4690-8060-86038f4f83af.html?599454753
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货