采购到货及时率统计

代码示例

DECLARE @dte1   DATETIME
DECLARE @dte2   DATETIME

DECLARE @int   INT
SELECT @int = count (1)
  FROM OPOR p0
 WHERE     p0.DocDate >= '[%0]'
       AND p0.DocDate <= '[%1]'
SET @dte1 = '[%0]'
SET @dte2 = '[%1]'

SELECT CASE WHEN t.indx = 1 THEN t.CardCode ELSE NULL END 供应商,
       CASE WHEN t.indx = 1 THEN t.CardName ELSE NULL END 名称,
       CASE WHEN t.indx = 1 THEN t.NumAtCard ELSE NULL END 供应商参考号,
       CASE WHEN t.indx = 1 THEN t.TotalOrder ELSE NULL END 总订单笔数,
       CASE WHEN t.indx = 1 THEN t.FinieshedOrder ELSE NULL END
          总及时笔数,
       CASE
          WHEN t.indx = 1
          THEN
             cast (
                cast (
                   (  cast (t.FinieshedOrder AS NUMERIC (19, 6))
                    / cast (t.TotalOrder AS NUMERIC (19, 6))
                    * 100.00) AS NUMERIC (19, 2)) AS NVARCHAR)
             + '%'
          ELSE
             NULL
       END
          供应商及时率,
       t.DocEntry 采购订单,
       t.VisOrder 行,
       t.ItemCode 物料,
       t.Dscription 描述,
       t.OrderQty 订单数量,
       t.ShipDate 要求交货日期,
       t.Received 按时完成数量,
       CASE WHEN t.Finieshed = 1 THEN '√' ELSE NULL END 已达成,
       t.PriceAfVAT 订单税后价,
       t.GTotal 订单总金额,
       t.PdnAmt 按时收货金额,
       t.SlpName 采购员
  FROM (SELECT t0.DocEntry,
               t0.CardCode,
               t0.CardName,
               t1.VisOrder + 1 VisOrder,
               t0.NumAtCard,
               t0.DocDate,
               t1.ItemCode,
               t1.Dscription,
               t1.ShipDate,
               t1.InvQty OrderQty,
               isnull (t5.PdnQty, 0) Received,
               t6.SlpName,
               CASE WHEN isnull (t5.PdnQty, 0) >= t1.InvQty THEN 1 ELSE 0 END
                  Finieshed,
               t1.PriceAfVAT,
               t1.GTotal,
               t5.PdnAmt,
               ROW_NUMBER ()
                  OVER (PARTITION BY t0.CardCode ORDER BY t0.DocEntry)
                  indx,
               sum (1) OVER (PARTITION BY t0.CardCode) TotalOrder,
               sum (
                  CASE
                     WHEN isnull (t5.PdnQty, 0) >= t1.InvQty THEN 1
                     ELSE 0
                  END)
               OVER (PARTITION BY t0.CardCode)
                  FinieshedOrder
          FROM OPOR t0
               INNER JOIN POR1 t1
                  ON t0.DocEntry = t1.DocEntry
               LEFT OUTER JOIN (SELECT t3.BaseEntry,
                                       t3.BaseLine,
                                       sum (t3.InvQty) PdnQty,
                                       sum (t3.GTotal) PdnAmt
                                  FROM OPDN t2
                                       INNER JOIN PDN1 t3
                                          ON t2.DocEntry = t3.DocEntry
                                       INNER JOIN por1 t4
                                          ON t3.BaseEntry = t4.DocEntry
                                             AND t3.BaseLine = t4.LineNum
                                 WHERE     t2.CANCELED = 'N'
                                       AND t3.BaseType = 22
                                       AND t2.DocType = 'I'
                                       AND t2.DocDate <= t4.ShipDate
                                       AND t2.DocDate <= @dte2
                                GROUP BY t3.BaseEntry, t3.BaseLine) t5
                  ON t1.DocEntry = t5.BaseEntry AND t1.LineNum = t5.BaseLine
               LEFT OUTER JOIN OSLP t6
                  ON t1.SlpCode = t6.SlpCode
         WHERE     t0.CANCELED = 'N'
               AND t1.ShipDate >= @dte1
               AND t1.ShipDate <= @dte2
               AND t0.DocType = 'I'
) t
ORDER BY t.CardCode, t.DocEntry, t.VisOrder