<pre name="code" class="sql"><pre name="code" class="delphi"><span style="color:#ff0000;">从今天天提前三天的没有交货的</span>
select
a.fpuremp,a.fpurno,b.frecdate,c.fprvnname,i.fgoodscode,[fgoodsnameexp]=case when i.fifcategory='1' then b.fgoodsname else i.fgoodsname end,[fsizedescexp]=case when i.fifcategory='1' then b.fsizedesc else i.fsizedesc end,b.finvqty,b.fbuyqty,[fbuyunitname]=k.funitname,b.frecqtyofbuyunit,[fnorecqtyofbuyunit]=case when b.finvqty-isnull(n.ftmprecqtyofinvunit,0)-b.frecqtyofinvunit+b.faddrtnqtyofinvunit>0 then dbo.fn_getunitexchange(b.fgoodsid,b.finvunit,b.fbuyunit,b.finvqty-isnull(n.ftmprecqtyofinvunit,0)-b.frecqtyofinvunit+b.faddrtnqtyofinvunit) else 0 end,b.frtnqtyofbuyunit,[fifcanceldesc]=dbo.fn_ifcanceldesc(a.fifcancel),b.fordno,b.forino,[fcflagdesc]=dbo.fn_cflagdesc(a.fcflag),[fifclosedesc]=dbo.fn_getendcasedesc(a.fifclose),p.fprinttimes
,fdiffday=datediff(day,b.frecdate,getdate())
From t_PURD_PurMst A with(nolock)
Left Join t_PURD_PurItem B with(nolock) on a.fpurno=b.fpurno
Left Join t_PURM_PrvMst C with(nolock) on a.fprvcode=c.fprvcode
Left Join t_BOMM_GoodsMst I with(nolock) on B.fGoodsID=i.fgoodsid
Left Join t_BMSM_UnitMst K with(nolock) on B.fBuyUnit=K.funitcode
Left Join V_PUR010_HaveTmpRecQty N with(nolock) on b.fpurno=n.fpurno and b.fsno=n.fpursno
Left Join t_ADMM_RptPrintLogSum P with(nolock) on P.fSrcFormCode='PUR010_FrmPurchaseOrder' and P.fKeyValue=a.fpurno
Left Join t_BOMM_GoodsLevelItem W with(nolock) on b.fgoodsid=w.fgoodsid and b.flvlcode=w.flvlcode
where ( a.fschtype= '1') and ((a.fifcancel = '0')
and (Convert(varchar(10),b.frecdate,121) <= convert(nvarchar(10),DateAdd(Day,3,GetDate()),120))
and ( (dbo. fn_getendcasedesc(a.fifclose) = N'未结案') or (dbo. fn_getendcasedesc(a.fifclose) = N'反结案'))) and ( a.fschtype= '1' )
and (b.frecqtyofbuyunit<b.fbuyqty)
--PurUsr~String|purno~String|DeliveryDate~Date|prvnname~String|GoodsCode~String|PingMing~String|sizedesc~String|purqty~Number|计价单位采购数~Number|buyunitname~String|计价单位已收数~Number|计价单位未收货数~Number|计价单位已退数~Number|Taxup~Price|discardsts~String|taxamt~Amount|OrdNo~String|SrcNo~String|GradeName~String|appsts~String|closests~String|prntimes~Number|逾期天数
到今天还没有交货的
select
a.fpuremp,a.fpurno,b.frecdate,c.fprvnname,i.fgoodscode,[fgoodsnameexp]=case when i.fifcategory='1' then b.fgoodsname else i.fgoodsname end,[fsizedescexp]=case when i.fifcategory='1' then b.fsizedesc else i.fsizedesc end,b.finvqty,b.fbuyqty,[fbuyunitname]=k.funitname,b.frecqtyofbuyunit,[fnorecqtyofbuyunit]=case when b.finvqty-isnull(n.ftmprecqtyofinvunit,0)-b.frecqtyofinvunit+b.faddrtnqtyofinvunit>0 then dbo.fn_getunitexchange(b.fgoodsid,b.finvunit,b.fbuyunit,b.finvqty-isnull(n.ftmprecqtyofinvunit,0)-b.frecqtyofinvunit+b.faddrtnqtyofinvunit) else 0 end,b.frtnqtyofbuyunit,[fifcanceldesc]=dbo.fn_ifcanceldesc(a.fifcancel),b.fordno,b.forino,[fcflagdesc]=dbo.fn_cflagdesc(a.fcflag),[fifclosedesc]=dbo.fn_getendcasedesc(a.fifclose),p.fprinttimes
,fdiffday=datediff(day,b.frecdate,getdate())
From t_PURD_PurMst A with(nolock)
Left Join t_PURD_PurItem B with(nolock) on a.fpurno=b.fpurno
Left Join t_PURM_PrvMst C with(nolock) on a.fprvcode=c.fprvcode
Left Join t_BOMM_GoodsMst I with(nolock) on B.fGoodsID=i.fgoodsid
Left Join t_BMSM_UnitMst K with(nolock) on B.fBuyUnit=K.funitcode
Left Join V_PUR010_HaveTmpRecQty N with(nolock) on b.fpurno=n.fpurno and b.fsno=n.fpursno
Left Join t_ADMM_RptPrintLogSum P with(nolock) on P.fSrcFormCode='PUR010_FrmPurchaseOrder' and P.fKeyValue=a.fpurno
Left Join t_BOMM_GoodsLevelItem W with(nolock) on b.fgoodsid=w.fgoodsid and b.flvlcode=w.flvlcode
where ( a.fschtype= '1') and ((a.fifcancel = '0')
and (Convert(varchar(10),b.frecdate,121) <= convert(nvarchar(10),DateAdd(Day,0,GetDate()),120))
and ( (dbo. fn_getendcasedesc(a.fifclose) = N'未结案') or (dbo. fn_getendcasedesc(a.fifclose) = N'反结案'))) and ( a.fschtype= '1' )
and (b.frecqtyofbuyunit<b.fbuyqty)
--PurUsr~String|purno~String|DeliveryDate~Date|prvnname~String|GoodsCode~String|PingMing~String|sizedesc~String|purqty~Number|计价单位采购数~Number|buyunitname~String|计价单位已收数~Number|计价单位未收货数~Number|计价单位已退数~Number|Taxup~Price|discardsts~String|taxamt~Amount|OrdNo~String|SrcNo~String|GradeName~String|appsts~String|closests~String|prntimes~Number|逾期天数