Grouping a table issue

Question

I have the table with four fields: ID, DateBeg, DateEnd, and RankID. RankID value is rank of the ID field over DateBeg asc. here's sample data:

ID |RankID |  DateBeg    |  DateEnd  |
---|-------|--------------------------
1  | 1     |01-01-2016   |04-01-2016 |
1  | 2     |05-01-2016   |11-02-2016 |
1  | 3     |12-02-2016   |15-02-2016 |
1  | 4     |16-02-2016   |19-02-2016 |
1  | 5     |23-02-2016   |25-02-2016 |
4  | 2     |05-01-2016   |07-01-2016 |
4  | 3     |08-01-2016   |12-01-2016 |
5  | 1     |04-01-2016   |06-01-2016 |

Now, I want to group the ID records, which have DateBeg value 1 day after the previous rank records DateEnd value(if it is null, it must be included too). the desired result of this example table will be:

ID | Min(DateBeg)|Max(DateEnd)|
---|-------------|----------------
1  |01-01-2016   |19-02-2016  |
1  |23-02-2016   |25-02-2016  |
4  |05-01-2016   |12-01-2016  |
5  |04-01-2016   |06-01-2016  |

Hope You can help me out, Thanks in advance.

What is the RankID for? This looks redundant, as the order is the same as the dates. Or does RankID have another special meaning? If it doesn't, I would remove it from the table. — Thorsten Kettner, Nov 02 '16 at 09:07

Tharunkumar Reddy · Answer 1 · 2016-11-02T11:23:16.917

3

Try Like below. I assume your datebeg and Dateend are uin date data type format. Otherwise you need to convert into date data type for comparision.

SELECT ID,MIN(DATEBEG),MAX(DateEnd) FROM
    (
    SELECT ID,(DATEBEG),ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) RNO,

    (DateEND),CASE WHEN DATEBEG=LAG( DATEADD(DAY,1,[DateEnd])) 
    OVER(PARTITION BY ID ORDER BY ID)THEN  1 END  NO
    FROM #TABLE1
    )A
    GROUP BY ID,ISNULL(NO,RNO)
    ORDER BY ID

Update: - Try below. It will work for all scenarios if you have unique ud and rank id combinations in your data set otherwise use row_number and generate unique numbers.

SELECT ID,MIN(DATEBEG),MAX(DATEEND) FROM 
(
SELECT ID,
       RANKID,
       datebeg, 
      DateEnd,
       CASE
         WHEN Dateadd(DAY, -1,  datebeg) = Lag( DateEnd) OVER(PARTITION BY ID ORDER BY ID)                                                        
       OR   Dateadd(DAY, 1, dateEND) = LEAD( DateBeg) OVER(PARTITION BY ID ORDER BY ID)                                                       
      THEN 1
         ELSE 0
       END NO
FROM   #Table1 
)A
GROUP BY ID,CASE WHEN NO=0 AND ID<>RANKID THEN RANKID ELSE NO END
ORDER BY ID

edited Nov 02 '16 at 11:23

answered Nov 02 '16 at 08:52

Tharunkumar Reddy

2,773
18
32

This doesn't work correctly. See here: http://sqlfiddle.com/#!6/f473ae/3 – Thorsten Kettner Nov 02 '16 at 10:03
@ThorstenKettner, You're right, after we will have the gaps, this query will return false results. – Tedo G. Nov 02 '16 at 10:13
@TedoG. Even above answer also gives false results. I am going to update my post soon. – Tharunkumar Reddy Nov 02 '16 at 10:19
@Buddi, I did not find any case when the above query is false, can you give an example? – Tedo G. Nov 02 '16 at 11:18
@TedoG.Couldn't able to recollect it that scenario. When i was testing above solution it fails. i am not sure what scenario it is. – Tharunkumar Reddy Nov 02 '16 at 11:59

Thorsten Kettner · Accepted Answer · 2016-11-02T09:58:15.890

Here is the algorithm:

Detect the gaps. Give each record that doesn't follow the previous records immediately a gap #1. So each record starting a new group is flagged then with a #1.
Build a running total of the gap numbers. Thus all records of a group get the same number.
Group by the groups found and show minimum start date and maximum end date per group.

The SQL Server query:

select id, min(datebeg), max(dateend)
from
(
  select
    id,
    datebeg,
    dateend,
    sum(gap) over (partition by id order by datebeg) as grp
  from
  (
    select 
      id,
      datebeg,
      dateend,
      case when datebeg <> 
                dateadd(day, 1, lag(dateend) over (partition by id order by datebeg)) 
           then 1 else 0 end as gap
    from mytable
  ) with_gap_flags
) with_group_numbers
group by id, grp
order by id, grp;

SQL fiddle: http://sqlfiddle.com/#!6/f473ae/1

I'm moving my accept to this answer, as this is much complete one. — Tedo G., Nov 02 '16 at 10:15

Grouping a table issue

2 Answers2