I have members, the group in which they belong and datetimes in which they were active. I want to find out which of the members had gap of more than 3 months between dates and I need to rank them.

The desired result is above with the rank; the table contains the data without the Rank column.

CodePudding user response：

One method is to use LAG to get the prior date, compared the 2 dates return 1 if it's more than 3 months, and then SUM those values in a windowed aggregate:

WITH CTE AS(
    SELECT header1,
           header2,
           CreateDate,
           CASE WHEN DATEDIFF(MONTH,LAG(CreateDate) OVER (PARTITION BY header2 ORDER BY CreateDate),CreateDate) > 3 THEN 1 ELSE 0 END AS Counter
    FROM (VALUES(11111,'EAM',CONVERT(datetime2(7),'2022-01-27 12:23:28.474000000')),
                (11111,'EAM',CONVERT(datetime2(7),'2022-08-25 10:41:15.500000000')),
                (11111,'EAM',CONVERT(datetime2(7),'2022-09-01 18:15:07.362000000')),
                (11111,'EAM',CONVERT(datetime2(7),'2022-09-08 13:03:38.859000000')),
                (11111,'EAM',CONVERT(datetime2(7),'2022-10-06 18:15:07.245000000')),
                (11111,'PEM',CONVERT(datetime2(7),'2022-07-25 10:41:15.500000000')),
                (11111,'PEM',CONVERT(datetime2(7),'2022-08-25 10:41:15.500000000')),
                (11111,'PEM',CONVERT(datetime2(7),'2022-09-26 13:03:38.859000000')))V(header1,header2,CreateDate))
SELECT header1,
       header2,
       CreateDate,
       SUM(Counter) OVER (PARTITION BY header2 ORDER BY CreateDate)   1 AS Rank
FROM CTE;

CodePudding user response：

select  header1 
       ,header2 
       ,Create_Date
       ,dense_rank() over(partition by header1, header2 order by flg) as Rank
from
(
select  *
       ,case when datediff(month, Create_Date, lead(Create_Date) over(partition by header1, header2 order by Create_Date)) >= 3 then 0 else 1 end as flg
from    t
) t

Fiddle