易飞有库存且超期15天未交订单的单身明细

select TC004 AS 客户编号,MA002 AS 客户简称,TD001 as 订单单别,TD002 as 订单单号,TD003 as 序号,TD004 as 品号,TD005 as 名称,TD006 as 规格,TD008 as 订单数量,TD009 as 已交数量,TD010
as 订单单位,MB064 as 库存数量,MB004 as 库存单位,TD013 as 订单交货日期
 FROM  COPTD
left join INVMB ON TD004=MB001
left join COPTC ON TC001=TD001 AND TC002=TD002
LEFT JOIN COPMA ON TC004=MA001
WHERE TD008>TD009 AND TD016='N' AND TD013<dateadd(dd,-15,GETDATE())
and TD001+TD002 NOT IN(SELECT TC001+TC002 FROM COPTC WHERE TC027='V') and (MB064-TD008+TD009)>0

 

问题有：

可能明细里整张单工单还是没有完成，所以部份明细没有交货有库存正常

 

但是可以提醒一下管理者找找超期这么久还是可以的