CODEWITHSUNDEEP
sqlgoogle-bigquery
yololo
yololo

Reputation: 319

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

Upvotes: 1

Views: 127

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Consider below approach

select *, 
  case row_number() over(partition by user_id, type, map_id order by open_date) 
    when 1 then 'new case'
    when count(1) over(partition by user_id, type, map_id) then 'last deferred case'
    else 'deferred case'
  end as status
from (
  select * except(new_case), 
    countif(new_case) over(partition by user_id, type order by open_date) as map_id
  from (
    select *, 
      open_date != lead(close_date) over(partition by user_id, type order by open_date desc) new_case
    from your_table
  )
)
-- order by open_date desc

if applied to sample data as in your question

with your_table as (
  select 'case12' case_id, '2021-06-01' open_date, '2021-08-25' close_date, 'user1' user_id, 'request' type union all
  select 'case11', '2021-05-01', '2021-06-01', 'user1', 'request' union all
  select 'case9', '2021-03-01', '2021-05-01', 'user1', 'request' union all
  select 'case7', '2020-09-15', '2020-10-31', 'user1', 'request' union all
  select 'case5', '2020-09-01', '2020-09-15', 'user1', 'request' union all
  select 'case3', '2020-02-12', '2020-09-01', 'user1', 'request' union all
  select 'case2', '2019-04-01', '2019-06-01', 'user1', 'request' union all 
  select 'case1', '2018-06-01', '2018-06-17', 'user1', 'request' 
)

the output is

enter image description here

Hope you can adopt above example to your real use case

Upvotes: 2

Related Questions