I'm trying to get to View 3 (InventoryTotalsSummed). Right now I rely on
Views 1 and 2 in order to do so. I get the correct results, but its too
slow. Does anyone have any ideas of how I can clean this up and make it
more efficient? I would greatly appreciate it. Let me know if you need
more info.
View 1 - InventoryCounts:
SELECT ProductID, FromID AS Type, - SUM(Quantity) AS Qty
FROM Inventory
GROUP BY ProductID, FromID
UNION
SELECT ProductID, ToID AS Type, SUM(Quantity) AS Qty
FROM Inventory
GROUP BY ProductID, ToID
View 2 - InventoryTotals:
SELECT PR.ProductID, ISNULL(SUM(TM.Received), 0) AS Received,
ISNULL(SUM(TM.Inventory), 0) AS Inventory,
ISNULL(SUM(TM.Quarantine), 0) AS Quarantine,
ISNULL(SUM(TM.Dead), 0) AS Dead, ISNULL(SUM(TM.Shipped),
0) AS Shipped, ISNULL(SUM(TM.Adjustment), 0)
AS Adjustment
FROM Products PR LEFT OUTER JOIN
(SELECT ProductID, SUM(Qty) AS Received, 0 AS Inventory,
0 AS Quarantine, 0 AS Dead, 0 AS Shipped,
0 AS Adjustment
FROM InventoryCounts
WHERE Type = 1
GROUP BY ProductID
UNION
SELECT ProductID, 0 AS Received, SUM(Qty) AS Inventory,
0 AS Quarantine, 0 AS Dead, 0 AS Shipped,
0 AS Adjustment
FROM InventoryCounts
WHERE Type = 2
GROUP BY ProductID
UNION
SELECT ProductID, 0 AS Received, 0 AS Inventory, SUM(Qty)
AS Quarantine, 0 AS Dead, 0 AS Shipped,
0 AS Adjustment
FROM InventoryCounts
WHERE Type = 3
GROUP BY ProductID
UNION
SELECT ProductID, 0 AS Received, 0 AS Inventory,
0 AS Quarantine, SUM(Qty) AS Dead, 0 AS Shipped,
0 AS Adjustment
FROM InventoryCounts
WHERE Type = 4
GROUP BY ProductID
UNION
SELECT ProductID, 0 AS Received, 0 AS Inventory,
0 AS Quarantine, 0 AS Dead, SUM(Qty) AS Shipped,
0 AS Adjustment
FROM InventoryCounts
WHERE Type = 5
GROUP BY ProductID
UNION
SELECT ProductID, 0 AS Received, 0 AS Inventory,
0 AS Quarantine, 0 AS Dead, 0 AS Shipped, SUM(Qty)
AS Adjustment
FROM InventoryCounts
WHERE Type = 6
GROUP BY ProductID) TM ON PR.ProductID = TM.ProductID
GROUP BY PR.ProductID
View 3 - InventoryTotalsSummed:
SELECT ProductID, SUM(Inventory) AS Inventory, SUM(Quarantine)
AS Quarantine
FROM (SELECT ProductID, Inventory, Quarantine
FROM InventoryTotals
UNION
SELECT ProductID, Inventory, Quarantine
FROM InventoryBaseCount) IB
GROUP BY ProductID