CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
Dee Rinos
Dee Rinos

Reputation: 15

Is there a window function or combination of window functions that I can use to get this desired result via TSQL

Take the below table for instance:

ORDER_KEY  |   STATUS_DATE   | TASK_CREATED_DATE |  TASK_KEY  | TASK_ROW_ID | TASK_REVISION_ID 
-----------|-----------------|-------------------|------------|-------------|------------------ 
1274796898 | 10/4/2019 18:19 | 10/4/2019 18:12   | 5277852673 |           1 |                  
1274796898 | 10/4/2019 18:19 | 10/4/2019 18:12   | 5277852674 |           2 |                  
1274796898 | 10/4/2019 18:19 | 4/7/2020 14:22    | 5277853673 |           3 |                1 
1274796898 | 10/4/2019 18:19 | 4/7/2020 14:22    | 5277853674 |           4 |                1 
1274796898 | 10/4/2019 18:19 | 4/7/2020 14:22    | 5277853675 |           5 |                1 
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:31   | 5277852586 |           1 |                  
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:31   | 5277852587 |           2 |                  
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:31   | 5277852588 |           3 |                  
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:32   | 5277852589 |           4 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855586 |           1 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855587 |           2 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855588 |           3 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855589 |           4 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855590 |           5 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:49   | 5277855587 |           6 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:49   | 5277856270 |           7 |                  
1274737647 | 10/4/2019 11:49 | 4/7/2020 14:10    | 5281403575 |           8 |                1 
1274737647 | 10/4/2019 11:49 | 4/7/2020 14:10    | 5281403576 |           9 |                1 
1274737647 | 10/4/2019 11:49 | 4/7/2020 14:12    | 5281403595 |          10 |                1 
1274737647 | 10/4/2019 11:49 | 4/14/2020 09:32   | 5281403599 |          11 |                2 
1274737647 | 10/4/2019 11:49 | 4/26/2020 13:10   | 5281403600 |          12 |                3

which can be reproduced with the following query:

with data as (
    select *
    from (
      values
      (1274796898, '10/4/2019 18:19', '10/4/2019 18:12', 5277852673,   1), 
      (1274796898, '10/4/2019 18:19', '10/4/2019 18:12', 5277852674,   2), 
      (1274796898, '10/4/2019 18:19', '4/7/2020 14:22', 5277853673,   3), 
      (1274796898, '10/4/2019 18:19', '4/7/2020 14:22', 5277853674,   4), 
      (1274796898, '10/4/2019 18:19', '4/7/2020 14:22', 5277853675,   5), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:31', 5277852586,   1), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:31', 5277852587,   2), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:31', 5277852588,   3), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:32', 5277852589,   4), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855586,   1), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855587,   2), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855588,   3), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855589,   4), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855590,   5), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:49', 5277855587,   6), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:49', 5277856270,   7), 
      (1274737647, '10/4/2019 11:49', '4/7/2020 14:10', 5281403575,   8), 
      (1274737647, '10/4/2019 11:49', '4/7/2020 14:10', 5281403576,   9), 
      (1274737647, '10/4/2019 11:49', '4/7/2020 14:12', 5281403595,  10), 
      (1274737647, '10/4/2019 11:49', '4/14/2020 09:32', 5281403599,  11), 
      (1274737647, '10/4/2019 11:49', '4/26/2020 13:10', 5281403600,  12)
    ) v (ORDER_KEY, STATUS_DATE, TASK_CREATED_DATE, TASK_KEY, TASK_ROW_ID)
)
select * from data;

The behaviour I'm ultimately looking to achieve is the TASK_REVISION_ID column output.
In the data above, it has been manually inputted for illustration purposes.

TASK_ROW_ID = ROW_NUMBER() OVER(PARTITION BY ORDER_KEY ORDER BY TASK_CREATED_DATE, TASK_KEY)

At an ORDER_KEY level, I want to increment the TASK_REVISION_ID in the following fashion:

  1. Set TASK_REVISION_ID to 1 when TASK_CREATED_DATE > STATUS_DATE
  2. Increment TASK_REVISION_ID when the difference in minutes between Current Row TASK_CREATED_DATE and Previous Row TASK_CREATED_DATE >= 5

Upvotes: 0

Views: 99

Answers (2)

GSerg
GSerg

Reputation: 78165

You only need to amend the original answer that shows the correct idea ever so slightly:

select
  d.ORDER_KEY, d.STATUS_DATE, d.TASK_CREATED_DATE, d.TASK_KEY, d.TASK_ROW_ID, 
  task_revision_id = (
    case when grp = 1
    then sum(case when datediff(mi, prev_tcd, TASK_CREATED_DATE) > 5 or grp_first = 1 then 1 else 0 end) over
                      (partition by d.order_key, d.grp order by task_row_id)
    end
  )
from
  (select d.*,
          lag(TASK_CREATED_DATE) over (partition by order_key order by task_row_id) as prev_tcd,
          case when task_created_date > status_date then 1 else 0 end as grp,
          case when lag(case when task_created_date > status_date then 1 else 0 end, 1, 0) over (partition by order_key order by task_row_id) = 0 then 1 else 0 end as grp_first
   from
      data d
  ) d 
order by
  order_key,
  task_row_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This answers the original version of the question.

What you are describing is captured by this query:

select (case when grp = 1
             then sum(case when prev_tcd > dateadd(minute, -5, TASK_CREATED_DATE) then 0 else 1 end) over
                      (partition by d.order_key, v.grp order by task_created_date)
        end),
       d.*
from (select d.*,
             lag(TASK_CREATED_DATE) over (partition by order_key order by TASK_CREATED_DATE) as prev_tcd
      from data d
     ) d cross apply
     (values ((case when task_created_date > status_date then 1 else 0 end))) v(grp)
order by 1, 2, 3;

Here is a db<>fiddle.

Upvotes: 3

Related Questions