CODEWITHSUNDEEP
sqlsql-server
TheEsnSiavashi
TheEsnSiavashi

Reputation: 1255

Query to Group By based on the Date order

I have a table such as:

enter image description here

I want to compress it with dates to get:

enter image description here

I was using 'GROUP BY' but it didn't work as it will combine all the 'T's. How can I enforce SQL Server to keep the order as its grouping?

Upvotes: 0

Views: 42

Answers (1)

GMB
GMB

Reputation: 222382

This is a typical gaps-and-island problem. You can assign row numbers to records within groups of records sharing the same name, and within groups sharing the same name and type. The difference between the row numbers give you the group each record belongs to.

Consider:

SELECT 
   name,
   MIN(date) FromDate,
   MAX(date) ToDate,
   type
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) rn1,
        ROW_NUMBER() OVER(PARTITION BY name, type ORDER BY date) rn2
    FROM mytable t
) x
GROUP BY name, rn1 - rn2, type
ORDER BY name, FromDate

Demo on DB Fiddle:

name | FromDate |   ToDate | type
:--- | -------: | -------: | :---
Jack | 19700715 | 19890831 | T   
Jack | 19910920 | 19920526 | N   
Jack | 19930409 | 20160411 | T

Upvotes: 1

Related Questions