The Problem
The scenario I will look at today will involve an Inventory table, which holds information regarding intial beginning inventory per product, along with subsequent inventory transactions. To make things a bit more simple, I’ll assume that first record for a given date will contain the beginning inventory, each record after the initial record will indicate Inventory being moved.
The required report is a listing of the Products by Date and the amount of Inventory remaining at the end of the day. Management wants to know when ineventory is getting low for specific products before the entire supply has been depleted. The code snippet below will create my Inventory table and load some sample data into it.
IF OBJECT_ID('Inventory') IS NOT NULL
DROP TABLE Inventory;
GO
CREATE TABLE [dbo].Inventory
(
InventoryID [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](150) NULL,
[InventoryDate] [datetime] NULL,
[InventoryCount] INT NULL
)
GO
INSERT INTO Inventory
(Product, InventoryDate, InventoryCount)
SELECT 'Computer', DATEADD(d, -5, GETDATE()), 5000
UNION ALL
SELECT 'Computer', DATEADD(d, -4, GETDATE()), 4000
UNION ALL
SELECT 'Computer', DATEADD(d, -3, GETDATE()), 3000
UNION ALL
SELECT 'Computer', DATEADD(d, -2, GETDATE()), 2000
UNION ALL
SELECT 'Computer', DATEADD(d, -1, GETDATE()), 1000
INSERT INTO Inventory
(Product, InventoryDate, InventoryCount)
SELECT 'BigScreen', DATEADD(d, -5, GETDATE()), 5000
UNION ALL
SELECT 'BigScreen', DATEADD(d, -4, GETDATE()), 2000
UNION ALL
SELECT 'BigScreen', DATEADD(d, -3, GETDATE()), 1900
UNION ALL
SELECT 'BigScreen', DATEADD(d, -2, GETDATE()), 1800
UNION ALL
SELECT 'BigScreen', DATEADD(d, -1, GETDATE()), 1000
INSERT INTO Inventory
(Product, InventoryDate, InventoryCount)
SELECT 'PoolTable', DATEADD(d, -5, GETDATE()), 5000
UNION ALL
SELECT 'PoolTable', DATEADD(d, -4, GETDATE()), 4500
UNION ALL
SELECT 'PoolTable', DATEADD(d, -3, GETDATE()), 3900
UNION ALL
SELECT 'PoolTable', DATEADD(d, -2, GETDATE()), 3800
UNION ALL
SELECT 'PoolTable', DATEADD(d, -1, GETDATE()), 2800
The tricky part about this report comes when trying to determine how much inventory is left after each transaction. For the PoolTable product above, there are 5000 units available for sale at the end of the first day. At the end of the second day, 4500 of those units have been sold, leaving only 500 units. At the end of the third day, 3900 units have been sold, which means that more inventory needs to be purchased. Because the next result is totally dependent on the preceeding results, it makes sense to try to use a cursor to loop through each records and store values in variables and temp tables, and just report the result at the end. However, with some crafty TSQL, cursors can be avoided.
In the example below, I use a common-table expression (CTE) and the DENSE_RANK() windowing function, two new features in SQL Server 2005, to recurse through the results and return the final output as one TSQL statement.
;WITH RecursiveCTE(RowNumber, Product, InventoryCount, InventoryDate, RemainingInventory, Ranking)
AS
(
SELECT *
FROM
(
SELECT
RowNumber = CAST(DENSE_RANK() OVER(PARTITION BY product ORDER BY InventoryDate ASC) AS INT),
Product, InventoryCount, InventoryDate, ValueColumn = InventoryCount, Ranking = 0
FROM Inventory sho
) G
WHERE G.RowNumber = 1
UNION ALL
SELECT
r.RowNumber, r.Product, r.InventoryCount, r.InventoryDate, c.RemainingInventory - r.InventoryCount, c.Ranking + 1
FROM RecursiveCTE c
JOIN
(
SELECT RowNumber = CAST(DENSE_RANK() OVER(PARTITION BY Product ORDER BY InventoryDate ASC) AS INT),*
FROM Inventory
)r ON c.Product = r.Product AND r.RowNumber = c.RowNumber + 1
)
SELECT Product, InventoryDate, InventoryCount, RemainingInventory
FROM RecursiveCTE
ORDER BY Product, InventoryDate
Using a recursive CTE is not the only way to accomplish the desired results. You can also make use of subqueries to return the same thing. In fact, the subquery used below significantly outperforms the recursive CTE example mentioned above.
SELECT First.Product,First.InventoryDate, First.InventoryCount, Outage= 2*MAX(Second.InventoryCount)-SUM(Second.InventoryCount)
FROM
(
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Product,InventoryDate ASC),*
FROM Inventory
) First
INNER JOIN
(
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Product,InventoryDate ASC),*
FROM Inventory
) Second
ON First.Product = Second.Product AND First.RowNumber >= Second.RowNumber
GROUP BY
First.Product, First.InventoryDate, First.InventoryCount
ORDER BY
First.Product, First.InventoryDate ASC
Why not use a cursor?
The two examples used above are by no means simple TSQL queries. You need a strong foundation of TSQL and of some new SQL Server 2005 features to avoid cursors. But, why should you avoid using a cursor for this problem? They are a little more simple to write and understand because all of the processing happens to one record at a time. However, the use of cursors require more code to write, typically more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time. The example code below uses cursors to achieve the same as the two queries above, but at the cost of a lot more code and a lot slower execution time.
IF OBJECT_ID('tempdb..#InventoryTemp') IS NOT NULL
DROP TABLE #InventoryTemp
DECLARE @First BIT, @RemainingInventory INT
DECLARE @Product VARCHAR(20), @InventoryID INT, @InventoryCount INT
SELECT * INTO #InventoryTemp
FROM Inventory
ALTER TABLE #InventoryTemp
ADD RemainingInventory INT
DECLARE ProductCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT Product FROM Inventory
OPEN ProductCursor
FETCH NEXT FROM ProductCursor
INTO @Product
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @First = 1
DECLARE InventoryCursor CURSOR FAST_FORWARD FOR
SELECT InventoryID, InventoryCount
FROM #InventoryTemp
WHERE Product = @Product
ORDER BY InventoryDate ASC
OPEN InventoryCursor
FETCH NEXT FROM InventoryCursor
INTO @InventoryID, @InventoryCounT
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @RemainingInventory = ISNULL(@RemainingInventory, @InventoryCount)
BEGIN
UPDATE #InventoryTemp
SET RemainingInventory = CASE WHEN @First = 1 THEN InventoryCount ELSE @RemainingInventory - @InventoryCount END
WHERE InventoryID = @InventoryID
SELECT @RemainingInventory = RemainingInventory
FROM #InventoryTemp
WHERE InventoryID = @InventoryID
END
SET @First = 0
FETCH NEXT FROM InventoryCursor
INTO @InventoryID, @InventoryCount
END
CLOSE InventoryCursor
DEALLOCATE InventoryCursor
FETCH NEXT FROM ProductCursor
INTO @Product
END
CLOSE ProductCursor
DEALLOCATE ProductCursor
SELECT * FROM #InventoryTemp
Conclusion
Cursors aren’t all bad. In fact, they can make some database problems a lot easier to solve. But, the next time you’re saddled with a problem and your first thought is to use a cursor to solve it, take a step back and really examine the problem. There is a chance that you can use a more efficient set-based approach to solve your problem.
0 comments:
Post a Comment