运城
select a.spbh,a.spmch,a.shpgg,a.shengccj,b.pici,b.pihao,b.baozhiqi,b.sxrq,c.rq as 采购日期,c.rkdj,g.hshj,d.danwbh,d.dwmch,c.ywy as cgy,b.ywlxr,c.rkshl,e.fukfs,c.rkshl-sum(b.shl) as xsshl,sum(isnull(b.shl,0)) kcshl,sum(b.shl*b.gebjj) kcje, case when datediff(day,c.rq,getdate())>=90 and datediff(day,c.rq,getdate())<180 then '90-180天' when datediff(day,c.rq,getdate())>=180 and datediff(day,c.rq,getdate())<270 then '180-270天' when datediff(day,c.rq,getdate())>=270 then '270天以上' end as 滞销帐期,datediff(day,c.rq,getdate()) 购进多少天还未销完 from sphwph b join spkfk a on a.spid=b.spid left join splsk c on c.djbh=left(b.pici,14) and c.dj_sn=replace(b.pici,left(b.pici,15),'') left join mchk d on c.dwbh=d.dwbh left join jh_rkhz e on e.djbh=left(b.pici,14) --left join (select pici,ywlxr,row_number() over (PARTITION BY pici order by gebjj desc) row from sphwph where ywlxr<>'') f on b.pici=f.pici and f.row=1 left join jh_rkmx g on g.djbh=left(b.pici,14) and g.dj_sn=replace(b.pici,left(b.pici,15),'') where b.shl<>0 group by a.spbh,a.spmch,a.shpgg,a.shengccj,b.pici,b.pihao,b.baozhiqi,b.sxrq,c.rq,c.rkdj,g.hshj,d.danwbh,d.dwmch,c.ywy,b.ywlxr,c.rkshl,e.fukfs
长治
select a.spbh,a.spmch,a.shpgg,a.shengccj,b.pici,b.pihao,b.baozhiqi,b.sxrq,b.caigrq as 采购日期,c.rkdj,b.hshj,d.danwbh,d.dwmch,c.ywy as cgy,c.rkshl,e.fukuanfs,c.rkshl-sum(b.shl) as xsshl,sum(isnull(b.shl,0)) kcshl,sum(b.shl*b.gebjj) kcje, case when datediff(day,b.caigrq,getdate())>=90 and datediff(day,b.caigrq,getdate())<180 then '90-180天' when datediff(day,b.caigrq,getdate())>=180 and datediff(day,b.caigrq,getdate())<270 then '180-270天' when datediff(day,b.caigrq,getdate())>=270 then '270天以上' end as 滞销帐期,datediff(day,b.caigrq,getdate()) 购进多少天还未销完 from sphwph b join spkfk a on a.spid=b.spid left join splsk c on c.djbh=left(b.pici,14) and c.dj_sn=replace(b.pici,left(b.pici,15),'') left join mchk d on c.dwbh=d.dwbh left join jh_rkhz e on e.djbh=left(b.pici,14) --left join (select pici,ywlxr,row_number() over (PARTITION BY pici order by gebjj desc) row from sphwph where ywlxr<>'') f on b.pici=f.pici and f.row=1 left join jh_rkmx g on g.djbh=left(b.pici,14) and g.dj_sn=replace(b.pici,left(b.pici,15),'') where b.shl<>0 and b.pici not like 'su%' group by a.spbh,a.spmch,a.shpgg,a.shengccj,b.pici,b.pihao,b.baozhiqi,b.sxrq,b.caigrq,c.rkdj,b.hshj,d.danwbh,d.dwmch,c.ywy,c.rkshl,e.fukuanfs
--因此品种批次特殊没有匹配，需增补
select a.spbh,a.spmch,a.shpgg,a.shengccj,b.pici,b.pihao,b.baozhiqi,b.sxrq,b.caigrq from spkfk a,sphwph b where a.spid=b.spid and b.shl<>0 and pici like 'su%'
