Welcome to Sign in | Help

Re: Determinare stoc la data...

  •  11-20-2007, 11:20 AM

    Re: Determinare stoc la data...

    Voi lua un exemplu bazat be tabelele din AdventureWorks, deoarece mi-e greu să înţeleg în tabelele tale care e tabela cu produse (şi ce legătură au partenerii în treaba asta). Să zicem că vrem să calculăm stocul la 31 dec 2002, iar pentru teste ne vom concentra pe produsul cu ID-ul 948 ("Front Brakes"). Datele relevante pot fi furnizate de următoarele query-uri:



    DECLARE @InventoryDate datetime
    DECLARE
    @ProductID int
    SET
    @ProductID=948
    SET @InventoryDate='20021231'

    SELECT p.ProductID, p.Name, poh.OrderDate as PurchaseOrderDate, pod.OrderQty as PurchaseQty
    FROM Production.Product p
    INNER JOIN Purchasing.PurchaseOrderDetail pod ON pod.ProductID=p.ProductID
    INNER JOIN Purchasing.PurchaseOrderHeader poh ON poh.PurchaseOrderID=pod.PurchaseOrderID
    WHERE poh.OrderDate<=@InventoryDate
    AND (p.ProductID=@ProductID OR @ProductID IS NULL)
    ORDER BY p.ProductID, poh.OrderDate

    ProductID   Name                PurchaseOrderDate       PurchaseQty
    ----------- ------------------- ----------------------- -----------
    948         Front Brakes        2002-02-25 00:00:00.000 550
    948         Front Brakes        2002-05-13 00:00:00.000 550
    948         Front Brakes        2002-09-25 00:00:00.000 550
    (3 row(s) affected)

    SELECT p.ProductID, p.Name, soh.OrderDate as SalesOrderDate, sod.OrderQty as SalesQty
    FROM Production.Product p
    INNER JOIN Sales.SalesOrderDetail sod ON sod.ProductID=p.ProductID
    INNER JOIN Sales.SalesOrderHeader soh ON soh.SalesOrderID=sod.SalesOrderDetailID
    WHERE soh.OrderDate<=@InventoryDate
    AND (p.ProductID=@ProductID OR @ProductID IS NULL)
    ORDER BY p.ProductID, soh.OrderDate

    ProductID   Name                SalesOrderDate          SalesQty
    ----------- ------------------- ----------------------- --------
    948         Front Brakes        2002-07-11 00:00:00.000 8
    948         Front Brakes        2002-08-01 00:00:00.000 4
    948         Front Brakes        2002-08-01 00:00:00.000 5
    948         Front Brakes        2002-08-04 00:00:00.000 1
    948         Front Brakes        2002-08-13 00:00:00.000 10
    948         Front Brakes        2002-08-21 00:00:00.000 2
    948         Front Brakes        2002-09-01 00:00:00.000 2
    948         Front Brakes        2002-09-08 00:00:00.000 2
    948         Front Brakes        2002-09-12 00:00:00.000 6
    948         Front Brakes        2002-09-13 00:00:00.000 2
    948         Front Brakes        2002-10-03 00:00:00.000 4
    948         Front Brakes        2002-10-13 00:00:00.000 1
    948         Front Brakes        2002-10-15 00:00:00.000 8
    948         Front Brakes        2002-12-01 00:00:00.000 2
    948         Front Brakes        2002-12-05 00:00:00.000 2
    948         Front Brakes        2002-12-14 00:00:00.000 3
    948         Front Brakes        2002-12-29 00:00:00.000 6
    (17 row(s) affected)

    Dacă facem join pur şi simplu, astfel:

    SELECT p.ProductID, p.Name,
          poh.OrderDate as PurchaseOrderDate, pod.OrderQty as PurchaseQty,
          soh.OrderDate as SalesOrderDate, sod.OrderQty as SalesQty
    FROM Production.Product p
    INNER JOIN Purchasing.PurchaseOrderDetail pod ON pod.ProductID=p.ProductID
    INNER JOIN Purchasing.PurchaseOrderHeader poh ON poh.PurchaseOrderID=pod.PurchaseOrderID
    INNER JOIN Sales.SalesOrderDetail sod ON sod.ProductID=p.ProductID
    INNER JOIN Sales.SalesOrderHeader soh ON soh.SalesOrderID=sod.SalesOrderDetailID
    WHERE soh.OrderDate<=@InventoryDate AND poh.OrderDate<=@InventoryDate
    AND (p.ProductID=@ProductID OR @ProductID IS NULL)
    ORDER BY p.ProductID, poh.OrderDate, soh.OrderDate

    atunci obţinem: 

    ProductID   Name           PurchaseOrderDate PurchaseQty SalesOrderDate SalesQty
    ----------- -------------------------------- ----------- -------------- --------
    948         Front Brakes   2002-02-25        550         2002-07-11     8
    948         Front Brakes   2002-02-25        550         2002-08-01     4
    948         Front Brakes   2002-02-25        550         2002-08-01     5
    948         Front Brakes   2002-02-25        550         2002-08-04     1
    948         Front Brakes   2002-02-25        550         2002-08-13     10
    948         Front Brakes   2002-02-25        550         2002-08-21     2
    948         Front Brakes   2002-02-25        550         2002-09-01     2
    948         Front Brakes   2002-02-25        550         2002-09-08     2
    948         Front Brakes   2002-02-25        550         2002-09-12     6
    948         Front Brakes   2002-02-25        550         2002-09-13     2
    948         Front Brakes   2002-02-25        550         2002-10-03     4
    948         Front Brakes   2002-02-25        550         2002-10-13     1
    948         Front Brakes   2002-02-25        550         2002-10-15     8
    948         Front Brakes   2002-02-25        550         2002-12-01     2
    948         Front Brakes   2002-02-25        550         2002-12-05     2
    948         Front Brakes   2002-02-25        550         2002-12-14     3
    948         Front Brakes   2002-02-25        550         2002-12-29     6
    948         Front Brakes   2002-05-13        550         2002-07-11     8
    948         Front Brakes   2002-05-13        550         2002-08-01     4
    948         Front Brakes   2002-05-13        550         2002-08-01     5
    948         Front Brakes   2002-05-13        550         2002-08-04     1
    948         Front Brakes   2002-05-13        550         2002-08-13     10
    948         Front Brakes   2002-05-13        550         2002-08-21     2
    948         Front Brakes   2002-05-13        550         2002-09-01     2
    948         Front Brakes   2002-05-13        550         2002-09-08     2
    948         Front Brakes   2002-05-13        550         2002-09-12     6
    948         Front Brakes   2002-05-13        550         2002-09-13     2
    948         Front Brakes   2002-05-13        550         2002-10-03     4
    948         Front Brakes   2002-05-13        550         2002-10-13     1
    948         Front Brakes   2002-05-13        550         2002-10-15     8
    948         Front Brakes   2002-05-13        550         2002-12-01     2
    948         Front Brakes   2002-05-13        550         2002-12-05     2
    948         Front Brakes   2002-05-13        550         2002-12-14     3
    948         Front Brakes   2002-05-13        550         2002-12-29     6
    948         Front Brakes   2002-09-25        550         2002-07-11     8
    948         Front Brakes   2002-09-25        550         2002-08-01     4
    948         Front Brakes   2002-09-25        550         2002-08-01     5
    948         Front Brakes   2002-09-25        550         2002-08-04     1
    948         Front Brakes   2002-09-25        550         2002-08-13     10
    948         Front Brakes   2002-09-25        550         2002-08-21     2
    948         Front Brakes   2002-09-25        550         2002-09-01     2
    948         Front Brakes   2002-09-25        550         2002-09-08     2
    948         Front Brakes   2002-09-25        550         2002-09-12     6
    948         Front Brakes   2002-09-25        550         2002-09-13     2
    948         Front Brakes   2002-09-25        550         2002-10-03     4
    948         Front Brakes   2002-09-25        550         2002-10-13     1
    948         Front Brakes   2002-09-25        550         2002-10-15     8
    948         Front Brakes   2002-09-25        550         2002-12-01     2
    948         Front Brakes   2002-09-25        550         2002-12-05     2
    948         Front Brakes   2002-09-25        550         2002-12-14     3
    948         Front Brakes   2002-09-25        550         2002-12-29     6
    (51 row(s) affected)

    şi se vede clar că datele au fost duplicate.

    Dacă facem SUM pe aceste date, vom obţine rezultate incorecte:

    ProductID   Name                     PurchaseQty SalesQty
    ----------- ------------------------ ----------- -----------
    948         Front Brakes             28050       204
     

    O soluţie ar fi să folosim subquery-uri:

    SELECT p.ProductID, p.Name,
          COALESCE((
                SELECT SUM(pod.OrderQty)
                FROM Purchasing.PurchaseOrderDetail pod
                INNER JOIN Purchasing.PurchaseOrderHeader poh
                ON poh.PurchaseOrderID=pod.PurchaseOrderID
                WHERE pod.ProductID=p.ProductID AND poh.OrderDate<=@InventoryDate
          ),0) as PurchaseQty, COALESCE((
                SELECT SUM(sod.OrderQty)
                FROM Sales.SalesOrderDetail sod
                INNER JOIN Sales.SalesOrderHeader soh
                ON soh.SalesOrderID=sod.SalesOrderDetailID
                WHERE sod.ProductID=p.ProductID AND soh.OrderDate<=@InventoryDate
          ),0) as SalesQty
    FROM Production.Product p
    WHERE (p.ProductID=@ProductID OR @ProductID IS NULL)
    ORDER BY p.ProductID

     

    iar altă soluţie ar fi să facem GROUP BY-uri pe rând:
     

    SELECT p.ProductID, p.Name,
          COALESCE(po.PurchaseQty,0) AS PurchaseQty,
          COALESCE(so.SalesQty,0) AS SalesQty
    FROM Production.Product p
    LEFT JOIN (
          SELECT pod.ProductID, SUM(pod.OrderQty) as PurchaseQty
          FROM Purchasing.PurchaseOrderDetail pod
          INNER JOIN Purchasing.PurchaseOrderHeader poh
          ON poh.PurchaseOrderID=pod.PurchaseOrderID
          WHERE poh.OrderDate<=@InventoryDate
          GROUP BY pod.ProductID
    ) po ON po.ProductID=p.ProductID
    LEFT JOIN (
          SELECT sod.ProductID, SUM(sod.OrderQty) as SalesQty
          FROM Sales.SalesOrderDetail sod
          INNER JOIN Sales.SalesOrderHeader soh
          ON soh.SalesOrderID=sod.SalesOrderDetailID
          WHERE soh.OrderDate<=@InventoryDate
          GROUP BY sod.ProductID
    ) so ON so.ProductID=p.ProductID
    WHERE (p.ProductID=@ProductID OR @ProductID IS NULL)
    ORDER BY p.ProductID

    În ambele cazuri, obţinem rezultatul dorit, anume:

    ProductID   Name                     PurchaseQty SalesQty
    ----------- ------------------------ ----------- -----------
    948         Front Brakes             1650        68

    Răzvan 

View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems