采购订单执行统计之入库未完成 SQL查询

chenchenjie

/*经过多次沟通与试验
《采购订单执行统计之入库未完成 SQL查询》目前已基本完成了，而且我已通过服务器 建立存储过程与配置 sql mail 让服务器每天自动执行以下命令，并将结果导出为EXCEL文件 发给指定人员，以便跟踪采购订单的欠料情况。
*/
select f.cmemo AS 采购订单备注,f.cpoid AS 采购订单号,convert(char(10),f.dpodate,3) AS 采购日期,cast(d.iquantity as int) AS 采购数量,datediff(DAY,convert(varchar(10),b.dpodate,121),convert(varchar(10),getdate(),121))
,c.cpersonname AS 采购员,d.cinvcode AS 存货编码,h.cinvname AS 存货名称,
(case when d.cdefine22 is null then '-'else d.cdefine22 end) AS 存货自定义备注,i.cComUnitName AS 单位,g.cvenname AS 供应商名称,
(case when a.ccuscode is null then'-'else a.ccuscode end) AS 客户代码,cast(sum(isnull(e.iQuantity,0)) as int) as 入库数量,
(CASE WHEN a.Csocode is null then '-' else a.csocode end) AS 销售订单号
,(case when a.cmaker is null then '-' else a.cmaker end) AS 销售制单人
,(case when a.dpredatebt is null then '-' else convert(char(10),a.dpredatebt,3) end) as 预发货日期

from so_somain as a --销售订单主表
right JOIN CopyPOList as b ON a.csocode=b.csocode  --单据拷贝视图   销售订单号相等
inner join po_pomain as f on f.cpoid =b.cordercode  --采购订单主表  采购订单号相等
inner join person as c on c.cpersoncode=f.cpersoncode  --人员档案表  采购人员代码相等
inner join po_podetails as d on d.poid = f.poid  --采购订单子表  采购主表标识相等
left join rdrecords as e on e.iposid=d.id   --收发记录子表  采购子表标识相等
inner join Vendor as g on g.cvencode=f.cvencode  --供应商档案表  供应商代码相等
inner join inventory as h on h.cinvcode=d.cinvcode  --存货档案表  存货编码相等
inner join ComputationUnit as i on i.cComunitCode=h.cComunitCode  --计量单位档案表

Where --convert(char(10),b.dpodate,121)=convert(char(10),getdate()-15,3) and
(d.poid in (select poid from po_pomain where (poid in (select poid from po_podetails,rdrecords where
po_podetails.iQuantity>(select sum(rdrecords.iQuantity)
from rdrecords where rdrecords.iposid=po_podetails.id)
and POID IN (select poid from po_pomain where cState=1)))
or (poid in (select poid from po_podetails where id not in (select iposid from rdrecords where
id in (SELECT id FROM RDRECORD WHERE CSOURcE='采购到货单' ))))and cstate='1' ))
group by e.iposid,
f.cpoid
,a.ccuscode ,a.csocode  ,
a.cmaker,g.cvenname ,
c.cpersonname ,d.cinvcode ,h.cinvname ,
d.cdefine22 ,i.cComUnitName ,d.iquantity,
convert(char(10),f.dpodate,3) ,datediff(DAY,convert(varchar(10),b.dpodate,121),convert(varchar(10),getdate(),121)),f.cmemo,a.dpredatebt
order by f.cpoid

该贴已经同步到 chenchenjie的微博