Building Ranges using T-SQL

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.




Related Articles :

One Response to “Building Ranges using T-SQL”

  1. If you’re going to use CTE’s it would be easier/more readable to create an aggregate table rather than use recursion:

    with runaggs as (
    select
    p1.Prod_ID,
    rtot = isnull(sum(p2.Prod_Qty), 0)
    from product p1
    left join product p2
    on p2.Prod_ID < p1.Prod_ID
    group By p1.Prod_ID
    )
    select p.Prod_ID, p.Prod_Name, p.Prod_Qty, rtot,
    MinID = case when Prod_Qty = 0 then null else rtot+1 end,
    MaxID = case when Prod_Qty = 0 then null else rtot+Prod_Qty end
    FROM product p
    inner join runaggs r on r.Prod_ID = p.Prod_id

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |