'SQL - map rows based on dates
I'm looking at some case data that are not explicitly linked using IDs, etc. We can tell that two cases are linked if the open_date of the more recent case matches the close_date of another case and the two cases have the same type (request). I would like to check for any case if it has been deferred in the past and how many times it has been deferred. For example, case12 below was deferred twice - once from case11 which itself was deferred from case 9. And case7 was also deferred twice while case1 and case2 were never deferred. I think this would require a recursive solution but not sure how to implement.
case_id open_date close_date user_id type status
case12 2021-06-01 2021-08-25 user1 request complete
case11 2021-05-01 2021-06-01 user1 request deferred
case9 2021-03-01 2021-05-01 user1 request deferred
case7 2020-09-15 2020-10-31 user1 request saved
case5 2020-09-01 2020-09-15 user1 request deferred
case3 2020-02-12 2020-09-01 user1 request deferred
case2 2019-04-01 2019-06-01 user1 request partial
case1 2018-06-01 2018-06-17 user1 request partial
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
