How to group these records?

Database

How to group these records?

Input Data:

SkId NK1 NK2 StrtDt         EndDt
2457989 363 166476 22/03/2006 12/04/2006
2457990 363 166476 19/04/2006 19/04/2006
2457991 363 166476 22/04/2006 26/04/2006
2457992 363 166476 10/05/2006 3/06/2006
2457994 363 166476 23/06/2006 23/06/2006
2457995 363 166476 25/06/2006 21/07/2006

SKId = Surrogate Key Id

NK: Natural Key

SkId      NK1   NK2      StrtDt       EndDt    Gap Days New GroupID
2457989 363 166476 22/03/2006 12/04/2006 7 1
2457990 363 166476 19/04/2006 19/04/2006 3 1
2457991 363 166476 22/04/2006 26/04/2006 14 1
2457992 363 166476 10/05/2006 3/06/2006 20 2
2457994 363 166476 23/06/2006 23/06/2006 2 3
2457995 363 166476 25/06/2006 21/07/2006 0 3

Expected Output:

I need to group the data by natural keys (NK1 and NK2) and sort by StrtDt (ASC) and calculate GAP Days.

GAP Days is difference between current record end date and next record start date. For last record, GAP days will always be zero. I am able to calculate the GAP days.

However, I am having trouble in defining "New Group Id". Rule is if GAP days for combination of NK's is less than 8 days then the records can be grouped together.

Tried Row_Number / RANK with reset option but some other tricks as well ....Not sure if there is an easy way to achieve it 

WIP:

SkId      NK1   NK2      StrtDt       EndDt     Gap Days   GrpThem
2457989 363 166476 22/03/2006 12/04/2006 7 1
2457990 363 166476 19/04/2006 19/04/2006 3 1
2457991 363 166476 22/04/2006 26/04/2006 14 0
2457992 363 166476 10/05/2006 3/06/2006 20 0
2457994 363 166476 23/06/2006 23/06/2006 2 1
2457995 363 166476 25/06/2006 21/07/2006 0 1
3 REPLIES
Enthusiast

Re: How to group these records?

From the explanation , it seems, that once you get difference of days, it looks ok, if you just put something like this

case when gp_days < 8 then 1 else 0 end

Am I missing something?

Re: How to group these records?

Thanks for your reply. 

I am able to calculate this using case statement and have labelled it as "GrpThem" in WIP table.

I am not sure how to calculate "New Group Id" column  (shown in expected output) using SQL. 

I can do it using Stored Procedure but want to achieve this using SQL 

Senior Apprentice

Re: How to group these records?

The common technique to get this kind of result is using a cummulative sum on the 1/0 flag Raja mentioned:

SELECT ...
SUM(CASE WHEN GapDays >= 8 THEN 1 ELSE 0 end)
OVER (PARTITION BY NK1, NK2
ORDER BY StrtDt
ROWS UNBOUNDED PRECEDING) + 1
FROM
(
SELECT ...
COALESCE(MIN(StrtDt)
OVER (PARTITION BY NK1, NK2
ORDER BY StrtDt
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
- EndDt, 0) AS GapDays2
FROM table
) AS dt