Inserting rows based on conditions

Table Name: CaseHistory

I’m currently working with a dataset that aims to offer insights on customer support cases on a weekly basis. In the example above, you can see the progression of case 123376 from Week 1 till Week 5, amongst other cases. What I would like to be able to do is insert a row for a case for every week based on 2 conditions:

• the latest status change of the case was NOT answered in previous weeks
• this needs to be done only for weeks where there is no status change update for that case

For example, there is no status update for case 123376 in Week 4, and the previous status for this case was ‘Waiting’ in Week 3 (meaning the case is not answered and is still ongoing). Since, there is no status update for Case 123376 in Week 4, it gives the false impression that the case does not exist in the week 4 data. At the end, what we want to know is how many (overall) outstanding cases the customer support has per week (and not only the cases that had a status change within that week, but all previous weeks as well).

How can I add rows for cases on a week basis based on the above 2 conditions? Ideally, in the data for every week, I would like to look back at ALL previous cases and for all cases that did NOT have the last Status as ‘Answered’. I would greatly appreciate any guidance on how to approach and resolve this data problem? Thank you in advance.

Answer

If I understand correctly, you can fill in the missing weeks in various ways. I think a recursive CTE might be the simplest:

with cte as (
      select casenumber, status, date, week,
             lead(week) over (partition by casenumber order by week) as next_week, 0 as is_new
      from t
      union all
      select casenumber,
             (case when status = 'New' then 'Waiting' else status end),
             date,
             week + 1, 1
      from cte
      where week < next_week + 1
     )
select cte.*
from cte
where is_new = 1;

Notes:

• This does not add weeks after the last record for a given case. It is not clear if you need that (you might want to ask a new question if this is needed).
• This changes a “new” status to something else, so 'New' is not repeated.