Hoping someone can help me out with this problem.

I have the following sample dataset:

I am trying to create groupings based on MEM_ID. If a ADM_DT is equal to the previous DCHG_DT then the records should be grouped together

Below is the expected output:

I have attempted the following:

select DISTINCT MEM_ID
,CLM_ID
,ADM_DT
,DCHG_DT
,CASE WHEN ADM_DT = LAG(DCHG_DT) OVER(PARTITION BY MEM_ID ORDER BY ADM_DT, DCHG_DT) THEN 0 ELSE 1 END AS ISSTART
FROM 
table

Which produces something like this:

I have also looked into other external sources such as https://www.kodyaz.com/t-sql/sql-query-for-overlapping-time-periods-on-sql-server.aspx

This got me pretty close but I realized that the author was using a recursive CTE and Netezza does not support that function.

Ultimately I would like to create these groupings so that i can then merge to the original table that I am using and sum values based on the assigned group for each MEM_ID.

Thank you in advance for any help provided.

CodePudding user response：

Try this:

select MEM_ID, CLM_ID, ADM_DT, DCHG_DT,
sum(ISSTART) over(partition by MEM_ID order by ADM_DT, DCHG_DT rows unbounded preceding) as GROUP_ID from
(select MEM_ID
,CLM_ID
,ADM_DT
,DCHG_DT
,CASE WHEN ADM_DT = LAG(DCHG_DT) OVER(PARTITION BY MEM_ID ORDER BY ADM_DT, DCHG_DT) THEN 0 ELSE 1 END AS ISSTART
FROM 
table_name) t

Fiddle

Basically using your ISSTART in a sum to get the desired output.