Tedo G. Tedo G. - 1 month ago 4
SQL Question

Groupping a table issue

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.

Answer

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
Comments