[FACT_采购信息]增加了延期天数

【延期天数】是指的采购单上的货品交货日期 减 [厂家来货]单据货品第一次到货日期。

[FACT_采购信息]

SELECT p.[Purchase_ID]                                                                                                                                                                                  [采购单号ID],
       p.[Supply_No]                                                                                                                                                                                    AS [供应商ID],
       pg.[Goods_no]                                                                                                                                                                                    AS [货品ID],
       g.[Brand]                                                                                                                                                                                        AS [品牌ID],
       g.[Category]                                                                                                                                                                                     AS [类别ID],
       pd.[ColorID]                                                                                                                                                                                     AS [颜色ID],
       CONVERT(VARCHAR(8), p.[Purch_Date], 112)                                                                                                                                                         AS [日期ID],
       CONVERT(VARCHAR(8), pg.Deliver_Date, 112)                                                                                                                                                         AS [日期2ID],
       Isnull(S1, 0) + Isnull(S2, 0) + Isnull(S3, 0) + Isnull(S4, 0) + Isnull(S5, 0) + Isnull(S6, 0) + Isnull(S7, 0) + Isnull(S8, 0) + Isnull(S9, 0) + Isnull(S10, 0) + Isnull(S11, 0)                  AS [采购数量],
       ( Isnull(S1, 0) + Isnull(S2, 0) + Isnull(S3, 0) + Isnull(S4, 0) + Isnull(S5, 0) + Isnull(S6, 0) + Isnull(S7, 0) + Isnull(S8, 0) + Isnull(S9, 0) + Isnull(S10, 0) + Isnull(S11, 0) ) * pg.[price] AS [采购金额],
       1                                                                                                                                                                                                AS [采购货号计数]
      , --pr.Receipt_Date
      datediff(day, pr.Receipt_Date,pg.Deliver_Date) as [延期天数]
FROM   [Purchase] p
       LEFT JOIN [PurchaseGoods] pg
         ON pg.[Purchase_ID] = p.[Purchase_ID]
       LEFT JOIN [PurchaseDetail] pd
         ON pd.[PurchaseGoodsID] = pg.[PurchaseGoodsID]
       LEFT JOIN Goods g
         ON g.Goods_no = pg.Goods_No 
       
       left join (select Purchase_ID,prg.Goods_No,min(pr.Receipt_Date) Receipt_Date from [PuReceipt] pr
        left join PuReceiptGoods prg on pr.PureceiptID=prg.PureceiptID
        group by Purchase_ID,Goods_No) pr  
        on pr.Purchase_ID=p.Purchase_ID and pr.Goods_No=pg.Goods_No

WHERE  p.Posted = 1 
and p.Purchase_ID='PGZ0000617'

生产/采购单：Purchase

厂家来货：PuReceipt