Time limit no DELIVERY SQL statement

<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|逾期天数

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值