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