[易飞]七、邮件提醒-订单与销货单提醒

1.未结束订单(>6天)

SELECT T.TD001+'-'+rtrim(T.TD002)+'-'+T.TD003 单号,K.TC012 客户单号, T.TD004 品号, T.TD005 品名, T.TD006 规格, 
convert( decimal(15,2),T.TD008) 订单, convert( decimal(15,2),T.TD009) 已交, convert( decimal(15,2),(TD008-TD009)) 欠交, T.TD013 预交货日期, 
DATEDIFF(DAY,TD013,GETDATE()) AS 交货日超7天,A.MA002 客户简称,convert( decimal(15,2),B.MB064) 现有库存, case when (TD008-TD009)-B.MB064>0.00 then  convert( decimal(15,2),(TD008-TD009)-B.MB064) else 0.00 end as  欠数
FROM ZE.dbo.COPTC K, ZE.dbo.COPTD T, ZE.dbo.COPMA A, ZE.dbo.INVMB B 
WHERE (K.TC004=A.MA001) 
AND (T.TD021='Y') 
AND (T.TD008<>0) 
AND (T.TD016='N') 
AND (K.TC001=T.TD001) 
AND (K.TC002=T.TD002) 
AND B.MB001=T.TD004  
and TD013<=convert(char(8),getdate(),112)
and DATEDIFF(DAY,TD013,GETDATE()) >6

2.超期订单

SELECT K.TC004 客户编码,MA002 客户简称,T.TD001+'-'+rtrim