按照日期供应商统计出采购发票的发生金额和未付款金额,并列出明细 来源:http://blog.csdn.net/stxuhui/article/details/1445211 CREATE proc stkd_未付款明细表 --stkd_未付款明细表 '','','','' @begsu varchar(80), @endsu varchar(80), @begitem varchar(80), @enditem varchar(80), @begdate datetime, @enddate datetime, @is int as set nocount on select @endsu='zzzzz' where @endsu='' select @enditem='zzzzzz' where @enditem='' create table #aa( fsupplyid int, fsunumber varchar(80), fsuname varchar(80), fbillno varchar(80), fentryid int, fitemid int, fnumber varchar(80), fname varchar(80), findate datetime, fqty decimal(18,2) default(0), fprice decimal(18,2) default(0), famount decimal(18,2) default(0), fw decimal(18,2) default(0), fy decimal(18,2) default(0), ft decimal(18,2) default(0), fhj decimal(18,2) default(0) ) insert into #aa(fsupplyid,fsunumber,fsuname,fbillno,fentryid,fitemid,fnumber,fname,findate,fqty,fprice,famount,fw,ft) select ip.fsupplyid,ts.fnumber,ts.fname,ip.fbillno,ipe.fentryid,ipe.fitemid,ti.fnumber,ti.fname,ic.fdate ,ipe.fqty,ipe.fprice,case when ipe.famount>0 then ipe.famount else 0 end, trp.fremainamount,case when ipe.famount<0 then -ipe.famount else 0 end from icpurchase ip inner join icpurchaseentry ipe on ip.finterid=ipe.finterid inner join t_supplier ts on ip.fsupplyid=ts.fitemid inner join t_icitem ti on ipe.fitemid=ti.fitemid left join icstockbill ic on ipe.fsourceinterid=ic.finterid and ipe.fsourcetrantype=1 left join icstockbillentry ice on ipe.fsourceinterid=ice.finterid and ipe.fsourceentryid=ice.fentryid and ipe.fitemid=ice.fitemid inner join t_rp_contact trp on ip.finterid=trp.finvoiceid and ftype=4 where ts.fnumber>=@begsu and ts.fnumber<=@endsu and ti.fnumber>=@begitem and ti.fnumber<=@enditem and ip.fdate>=@begdate and ip.fdate<=@enddate insert into #aa(fsupplyid,fsunumber,fsuname) select fsupplyid,fsunumber+'合计',fsuname+'合计' from #aa group by fsupplyid,fsunumber,fsuname update t1 set t1.fy=t2.famount from #aa t1, (select fcustomer,sum(fremainamount) as famount from t_rp_contact where fremainamount>0 and ftype=6 group by fcustomer) t2 where t1.fsupplyid=t2.fcustomer and t1.fsunumber like '%合计' update t1 set t1.famount=t2.famount from #aa t1, (select fsupplyid,sum(famount) as famount from #aa group by fsupplyid) t2 where t1.fsupplyid=t2.fsupplyid and t1.fsunumber like '%合计' update t1 set t1.fw=t2.famount from #aa t1, (select fcustomer,sum(fremainamount) as famount from t_rp_contact where fremainamount>0 and ftype in (2,4) group by fcustomer) t2 where t1.fsupplyid=t2.fcustomer and t1.fsunumber like '%合计' update #aa set fw=0,fy=0 where fentryid>1 update #aa set fhj=fw-fy+ft where fsunumber like '%合计' if(@is=0) begin delete from #aa where fsupplyid in ( select fsupplyid from #aa where fsunumber like '%合计' and fhj=0) end select fsunumber as 供应商代码,fsuname as 供应商名称,fbillno as 发票号,fnumber as 物料代码,fname as 物料名称, findate as 入库日期,fqty as 数量,fprice as 单价,famount as 金额,fw as 未付款金额,fy as 预付金额, ft as 退货金额,fhj as 合计金额 from #aa order by fsunumber,fbillno,fnumber drop table #aa GO |
|