All Forums Database
shori27 2 posts Joined 09/14
24 Sep 2014
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

 

Raja_KT 1246 posts Joined 07/09
24 Sep 2014

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?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

shori27 2 posts Joined 09/14
25 Sep 2014

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 

dnoeth 4628 posts Joined 11/04
25 Sep 2014

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

 

Dieter

You must sign in to leave a comment.