We often use excel for any running repetitive calculations since it is a very convenient user friendly interface which allows us to just enter a formula in a cell and simply drag and drop the formulas to generate a complete output.
For example, I had a Products listing, with ID and Quantity for each product. In this example, MinID and MaxId are the minimum and maximum id range of a given product quantity. Say we have a quantity of 50 for Product A then we will need to set the MinId as 1 and MaxID as 50, and if there is a quantity of 10 for product B then it should return a running range from product A so that the MinID is 51 and MaxID is 60. This range is generally required when we have a sequential product list to be updated in another table and the quantity is fixed.
As shown in the above Excel image, I have copied the products with their quantity. Next, to get the Minimum product and maximum product range based on the quantity I had applied a formula.
By applying these formulas on all the products, we will get a range having Minimum and Maximum ID for all the products
Here, Product ProdG has a 0 Quantity. So we need a formula in these columns again to make the range of MinID and MaxID set to 0.
However, it would be much more efficient if we could find a TSQL solution to this problem.
Solution
To begin with the solution, let us create a product table having all the required information shown in the example.
SELECT 1 AS Prod_ID ,'ProdA' AS Prod_Name,93 AS Prod_Qty INTO Product UNION ALL SELECT 2,'ProdB',67 UNION ALL SELECT 3,'ProdC',31 UNION ALL SELECT 4,'ProdD',52 UNION ALL SELECT 5,'ProdE',84 UNION ALL SELECT 6,'ProdF',19 UNION ALL SELECT 7,'ProdG',0 UNION ALL SELECT 8,'ProdH',4 UNION ALL SELECT 9,'ProdI',6 GO SELECT * FROM Product
Executing the script above creates a product table.
There can be many solutions to this problem. I will demonstrate here two specific solutions based on different version of SQL Server. One on SQL Server 2000 and other on later versions.
SQL Server 2000 Solution
The first thing for our solution is get the result of one row applied to the subsequent rows. This can only be achieved via a recursive query. In SQL Server 2000 we do not have the ability to run recursive queries. Hence, this can be achieved by CROSS JOIN:
SELECT P2.Prod_ID,P2.Prod_Name,P2.Prod_Qty, CASE WHEN P2.Prod_ID=1 THEN 0 ELSE P1.Prod_Qty END AS MinID, CASE WHEN P2.Prod_ID=1 THEN P1.Prod_Qty ELSE P2.Prod_Qty END AS MaxID FROM Product P1 CROSS JOIN Product P2 WHERE P1.Prod_ID <= P2.Prod_ID
OUTPUT:
By the query above we will get the cumulative product quantity for all the consecutive rows. Next we need to implement a bit of tricky TSQL to get the range.
SELECT Prod_ID,Prod_Name,Prod_Qty,
CASE WHEN MIN(MAXID) = 0 THEN 0 ELSE SUM(MINID) + 1 END AS MinID, CASE WHEN MAX(MaxID) = 0 THEN 0 ELSE (SUM(MINID)+MAX(MAXID)) END AS MaxID
FROM
(
SELECT P2.Prod_ID,P2.Prod_Name,P2.Prod_Qty,
CASE WHEN P2.Prod_ID=1 THEN 0 ELSE P1.Prod_Qty END AS MinID,
CASE WHEN P2.Prod_ID=1 THEN P1.Prod_Qty ELSE P2.Prod_Qty END AS MaxID
FROM Product P1 CROSS JOIN Product P2
WHERE P1.Prod_ID <= P2.Prod_ID
) Temp
WHERE MinID <> MaxID
GROUP BY Prod_ID,Prod_Name,Prod_Qty
OUTPUT:
To get the exact range, we will apply the same type of formula as in excel. For MinID, it is the Quantity returned by the inner loop. Whereas for MaxID, we will add the Quanitity to the previous row Quantity.
SQL Server 2005 (and above) Solution
In SQL Server 2005 and above we can use CTE (Common Table Expression) to handle recursive queries.
;WITH CTE AS
(
SELECT Prod_ID,Prod_Name,Prod_Qty, CAST(1 AS INT) AS MinID,
Prod_Qty AS MaxID,1 AS INIT
FROM Product
UNION ALL
SELECT p.Prod_ID,p.Prod_Name,p.Prod_Qty,c.MaxID+1,
c.MaxID+p.Prod_Qty,INIT+1
FROM CTE c
JOIN Product p ON c.Prod_ID +1=p.Prod_ID
)
SELECT Prod_ID,Prod_Name,Prod_Qty,
CASE WHEN Prod_Qty = 0 THEN 0 ELSE MAX(MinID) END MinID,
CASE WHEN Prod_Qty = 0 THEN 0 ELSE MAX(MaxID) END MaxID
FROM CTE
GROUP BY Prod_ID,Prod_Name,prod_qty
ORDER BY prod_ID
OUTPUT:/p>
CTE has made the recursive query easy to implement. In the final select we first check if the quantity is 0, set the MinID and MaxID to 0 as in the Excel solution.
Comparing the two solutions, performance wise the CTE solution is far better than CROSS JOIN solution. However, in some place you do not have the option to avoid CROSS JOIN when working on older versions of SQL Server.
]]>