K3成长版销售日报表.docVIP

set Nocount oncreate table #CK( fcustid int, FyesAmount decimal(12,2), FFPAmount decimal(12,2), FSKAmount decimal(12,2), FendAmount decimal(12,2) )insert into #CK(fcustid,FyesAmount)select v1.fcustid, sum(isnull(v2.famount,0) from icsale v1 inner join IcsaleEntry v2 on v1.finterid=v2.finterid where v1.fdate* group by v1.fcustidinsert into #CK(fcustid,FendAmount) select v1.fcustomer,sum(isnull(v1.famount,0) from t_RP_NewReceiveBill v1 where v1.fdate=* and v1.fdate=* and v1.fdate=2011-05-27 and v1.fdate=2011-05-27 group by v1.fcustid,v2.fitemid,v2.fbatchno,v2.FEntrySelfI0459,v2.FEntrySelfI0460,v2.fpricecreate table #end( fcustid int, fyesAmount decimal(12,2), FinAmount decimal(12,2), fnumber varchar(128), fname varchar(128), fmodel varchar(128), fpichi varchar(128), dengji varchar(128), jianshu int, fqty decimal(12,2), fprice decimal(12,2), famount decimal(12,2), fendamount decimal(12,2) )insert into #end(fcustid,fnumber,fname,fmodel,fpichi,dengji,jianshu,fqty,fprice,famount) select v1.fcustid,t1.fnumber,t1.fname,t1.fmodel,t2.fname,v1.dengji, count(v1.jingzhong), sum(v1.jingzhong),v1.fprice,sum(v1.famount) from #temp v1 inner join t_icitem t1 on v1.fitemid=t1.fitemid inner join t_item_3001 t2 on v1.fpichi=t2.fitemid inner join t_organization t3 on v1.fcustid=t3.fitemid group by v1.fcustid,t1.fnumber,t1.fname,t1.fmodel,t2.fname,v1.dengji,v1.fpriceupdate #end set fyesAmount=v2.FyesAmount-v2.fendAmount from #end v1 left join #HK v2 on v1.fcustid=v2.fcustidupdate #end set fendAmount=v2.FyesAmount+v2.FFPAmount-v2.fendAmount-v2.FSKAmount from #end v1 left join #HK v2 on v1.fcustid=v2.fcustidupdate #end set FinAmount=v2.FSKAmount from #end v1 left join #HK v2 on v1.fcustid=v2.fcustidcreate table #final( fcustomer varchar(128), fyesAmount decimal(12,2), finAmount decimal(12,2), fnumber varchar(128), fname varchar(128), fmodel varchar(128), fpichi varchar(128), dengji varchar(128), jianshu int, fqty decimal(12,2), fprice decimal(12,2), famount decimal(12,2), fendamount decimal(12,2) )insert into #final select t3.fname,v1.fyesAmount,v1.finAmount,v1.fnumber,v1.fname,v1.fmodel,v1.fpichi,v1.dengji,v1.jianshu,v1.fqty,v1.fprice,v1.famount,v1.fendamount from #end v1 inner join t_organization t3 on v1.fcustid=t3.fitemid insert into #final(fcustomer,fyesAmount,finAmount,jianshu,fqty,famount,fendamount) select 合计,sum(isnull(v1.fyesAmount,0),sum(isnull(v1.finAmount,0),count(v1.fqty),sum(v1.fqty),sum(isnull(v1.famount,0),sum(isnull(v1.fendamount,0) from #end v1select fcustomer as 客户, fyesAmount as 统计前欠款额, finAmount as 收款, fnumber as 物料代码, fname as 物料名称, fmodel as 规格, fpichi as 批次, dengji as 等级, jianshu as 件数, fqty as 重量, fprice as 单价,