CODEWITHSUNDEEP
sqlsql-server-2012gaps-and-islands
MDMP
MDMP

Reputation: 13

Identify Rank of date ranges from datetime column -- similar to previous post with an addition

I have members, the group in which they belong, file type and datetimes in which they were active. I want to find out which members based on the group and file type rankings when there is a gap of 1 month or more.

MID Group File Type Create_Date
123A EAM Partial 2022-01-16 12:23:28.474000000
123A EAM Full 2022-03-01 10:41:15.500000000
123A EAM Full 2022-04-15 10:41:15.500000000
123A EAM Full 2022-05-26 10:41:15.500000000
123A EAM Full 2022-09-20 10:41:15.500000000
123A EAM Full 2022-10-05 10:41:15.500000000

This is the outcome I am looking for:

MID Group File Type Create_Date Rank
123A EAM Partial 2022-01-16 12:23:28.474000000 1
123A EAM Full 2022-03-01 10:41:15.500000000 2
123A EAM Full 2022-04-15 10:41:15.500000000 2
123A EAM Full 2022-05-26 10:41:15.500000000 2
123A EAM Full 2022-09-20 10:41:15.500000000 3
123A EAM Full 2022-10-05 10:41:15.500000000 3

Upvotes: 1

Views: 49

Answers (1)

nbk
nbk

Reputation: 49385

The CTE will get you the rank number, it will give a 1 when the date is bigger as 1 month or a new group is in the database else it is 0 as no change of rank is needed

The Outer select then needs only to sum the numbers up

WITH CTE As (SELECT
[MID], [Group], [File Type], [Create_Date]
,CASE WHEN DATEDIFF(MONTH, LAG(Create_Date) OVER(PARTITION BY [Group] ORDER BY [Create_Date] ), [Create_Date]) > 1 
  OR DATEDIFF(MONTH, LAG(Create_Date) OVER(PARTITION BY [Group] ORDER BY [Create_Date] ), [Create_Date]) IS NULL then 1 ELSE 0 ENd new_number
  FROM tab1)
SELECT 
  [MID], [Group], [File Type], [Create_Date],
SUM(new_number) OVER(PARTITION BY [Group] ORDER BY [Create_Date] ) [Rank]
FROM CTE
MID Group File Type Create_Date Rank
123A EAM Partial 2022-01-16 12:23:28.473 1
123A EAM Full 2022-03-01 10:41:15.500 2
123A EAM Full 2022-04-15 10:41:15.500 2
123A EAM Full 2022-05-26 10:41:15.500 2
123A EAM Full 2022-09-20 10:41:15.500 3
123A EAM Full 2022-10-05 10:41:15.500 3

fiddle

Upvotes: 2

Related Questions