Batch in-store commodity analysis

Yuncheng

select a.spbh,a.spmch,a.shpgg,a.shengccj,b.pici,b.pihao,b.baozhiqi,b.sxrq,c.rq as  purchase date, C.RKDJ, G.hshj,d.danwbh,d.dwmch,c.ywy as cgy,b.ywlxr,c.rkshl,e.fukfs,c.rkshl-sum (B.SHL) &NBSP;AS&NBSP;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 days '  when datediff (day , C.rq,getdate ()) >=180 and datediff (Day,c.rq,getdate ()) <270 then  ' 180-270 days '  when  datediff (Day,c.rq,getdate ()) >=270 then  ' More than 270 days '  end as  slow-down billing period, DATEDIFF (day, C.rq,getdate ())   How many days have not sold out From sphwph bjoin spkfk a on a.spid=b.spidleft  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.dwbhleft join jh_rkhz e  on e.djbh=left (b. pici,14)--left join  (Select pici,ywlxr,row_number ()  over  (partition by  PICI&NBSP;ORDER&NBSP;BY&NBSP;GEBJJ&NBSP;DESC)  row from sphwph where ywlxr<> ")  f on b.pici=f.pici and f.row=1left 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<>0group &NBSP;BY&NBSP;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

Changzhi

select a.spbh,a.spmch,a.shpgg,a.shengccj,b.pici,b.pihao,b.baozhiqi,b.sxrq,b.caigrq as  Purchase date, C.rkdj,b.hshj,d.danwbh,d.dwmch,c.ywy as cgy,c.rkshl,e.fukuanfs,c.rkshl-sum (B.SHL) &NBSP;AS&NBSP;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 days '  when  datediff (Day,b.caigrq,getdate ()) >=180 and datediff (Day,b.caigrq,getdate ()) <270 then   ' 180-270 days '  when datediff (Day,b.caigrq,getdate ()) >=270 then  ' More than 270 days '  end  as  poor billing period, DateDiff (Day,b.caigrq,getdate ())   Purchase How many days have not sold out from sphwph bjoin spkfk  A on a.spid=b.spidleft 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.dwbhleft  join jh_rkhz e&nbsP;on e.djbh=left (b.pici,14)--left join  (Select pici,ywlxr,row_number ()  over  ( PARTITION&NBSP;BY&NBSP;PICI&NBSP;ORDER&NBSP;BY&NBSP;GEBJJ&NBSP;DESC)  row from sphwph where  ywlxr<> ")  f on b.pici=f.pici and f.row=1left 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

--Therefore, there is no match for the variety batch, additional

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% '

Batch in-store commodity analysis