K3成长版销售日报表.doc

K3成长版销售日报表.txt老公如果你只能在活一天，我愿用我的生命来延续你的生命，你要快乐的生活在提出分手的时候请不要说还爱我。K3成长版销售日报表.txt如果青春的时光在闲散中度过，那么回忆岁月将是一场凄凉的悲剧。杂草多的地方庄稼少，空话多的地方智慧少。即使路上没有花朵，我仍可以欣赏荒芜。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