1
 Customer_Id        Call_Date             Agent_M_Code    Row_Indicator
 810471698    2020-03-19 13:25:24.910       rmanzan2           1
 810471698    2020-03-22 20:28:19.067       pmaclair           2
 810471698    2020-03-24 09:22:47.833       njeanle            3
 810471698    2020-03-24 12:36:29.367       edelaro4           4
 810471698    2020-03-29 22:36:29.762       kdularo7           1
 810471698    2020-04-11 11:21:11.243       rbustam1           1
 810471698    2020-04-11 17:50:41.023       frenteri           2
 810471698    2020-05-10 11:16:21.683       cschuch2           1
 810471698    2020-05-13 15:26:40.660       gledesma           2
 810471698    2020-07-03 11:26:20.697       cmataver           1
 810471698    2020-07-22 14:19:53.450       irodri13           1

For the above table I need to generate the row_indicators but the condition here is... if the above Call_Date and below call_Date in between 10 Days then we need to generate the row_indicator in sequence i.e, (1,2,3,4..) if not again we need to start with 1.

For Example: In the above example table the first four rows are in between 10 days (Less than or equal to 240 Hrs) then for the first four rows the row_indicators are 1,2,3,4 and again from the fifth row the Call_Date is started from 1 because the fifth row date is not falling in the 10 days Call_Date range.

Dale K
  • 25,246
  • 15
  • 42
  • 71
venugopal
  • 79
  • 10
  • 2
    So, what is your question here? You haven't asked any questions, so we can't answer. What have *you* tried, and why didn't it work? – Thom A Sep 03 '20 at 08:45
  • 2
    What did you try so far? – Ilyes Sep 03 '20 at 08:45
  • I've tried with LEAD() with Partition by Order by Clause...but i have only be able to generate the row_numbers() to the table. Thank You @GMB :) your code is working for this scenario...Excellent answer. Sharp and effective once again thank you GMB . – venugopal Sep 04 '20 at 04:34

1 Answers1

3

You need a recursive query for this. The idea is to iteratively walk through the table by ascending call_date, while keeping track of the "first" record of each date. Whenever a row is more than 10 days later than the initial date, the value resets.

with 
    data as (select t.*, row_number() over(order by call_date) rn from mytable t),
    cte as (
        select d.*, call_date initial_date from data d where rn = 1
        union all
        select d.*, 
            case when d.call_date > dateadd(day, 10, c.initial_date) 
                then d.call_date
                else c.initial_date
            end
        from cte c
        inner join data d on d.rn = c.rn + 1
    )
select customer_id, call_date, agent_m_code, 
    row_number() over(partition by initial_date order by call_date) row_indicator
from cte
order by call_date
GMB
  • 216,147
  • 25
  • 84
  • 135