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

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.

image

Here is the result.

image

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: