T-SQL #47 – Build sequential ranges of dates with propagation to missing values using recursive CTE

Continuing from T-SQL #46.

Here is the second example from beyondrelational.com, their TSQL Challenge #12. I’ve modified their example with the hope that I will be able to come to a solution for the T-SQL challenge I am facing in real life.

The Context

I am working on an employee incentive project. Business wants to encourage the employees’ WIP (Work In Progress)  turns over quickly.  Inventory will be taken each business days to calculate the “age” of each account an employee has been working. Accounts will go through several stages in the WIP, and the account “aging” clock needs to stop at stage 3 since stage 3 is beyond the employee’s control.

The Challenge

Stopping the “aging” clock at stage 3 is actually pretty straightforward as long as we can capture when each account enters stage 3 and exits stage 3. The real challenge arises when I realized that an account can come in and out stage 3 multiple times in its lifetime in the WIP. So to account for this kind of “churning”, for each inventory date, I first set a flag “stage3”, to 1 if an account is in stage 3, or 0 when an account is not in stage 3. The purpose for the flag is so that I can later calculate the gap between stage 3. When I stop the aging clock at stage 3, I still need to add the gap between stage 3 to the age.

You would think that this stage 3 flag will help me to get the gap between stage 3. Yes, it does. But we having missing dates in the inventory dates, since we do not take inventory for holidays and weekends.

So what I need are:

1) Create a new inventory record for each missing inventory date between two valid inventory dates of the table @stage3.
2) For each new inventory date created, I need to recopy the stage3 indicator (1 or 0) from the previous inventory date.
3) Continue the list until the last inventory date on table @stage3 reached for each acct_id.

To make this example simple to understand, I’ll ignore the account number. Again, I put my comments here.

Here is the result.

I am still not there yet for my real problem, because each account has a different list of stage 3 indicators. I’ll come to that in my next blog.

T-SQL #46 – Generate a date series using a recursive CTE

I recently had a need to use recursive Common Table Expression (CTE)  query to meet a real business requirement.

Before I get into my real business situation, I want to blog about a couple of simple examples of using recursive CTE I found on beyondrelational.com (Google tsql challenges). I don’t who those people are, and how they collaborate on their work of providing those T-SQL challenges and picking winners for best solutions, but they are doing a fantastic job of promoting SET based query writing skills. According to this article T-SQL Challenge #1 on SQLServerCentral.com, TSQL Challenge Committee is a group of SQL Server experts from around the world. Many of them are SQL Server MVPs, some are SQL Server consultants and others are developers working with SQL Server for a number of years.

Anyway, here is the one simple example from beyondrelational.com of generating a date series using a recursive CTE. I put my comments here to make a couple of points.

   

1) Using UNION ALL and refer to the CTE makes the query recursive.

2) It’s important to know when to terminate the recursive query. The WHERE clause here serves the purpose of terminating the recursive part by setting a upper limit for the date.

If you are new to writing SQL queries, this might not make much sense to you. You might wonder why cannot you use a cursor or a WHILE loop. Well, the purpose here is to write SET based query rather than record-by-record sequential “program”.

Even a simple example like this has real life usage. You can use this date series to do a Cross Joint (Cartesian Product) with a dimension table, such as Product, to create a product catalog.