CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
user2823833
user2823833

Reputation: 105

Max dates for each sequence within partitions

I would like to see if somebody has an idea how to get the max and min dates within each 'id' using the 'row_num' column as an indicator when the sequence starts/ends in SQL Server 2016.

The screenshot below shows the desired output in columns 'min_date' and 'max_date'.

sample dataset

Any help would be appreciated.

Upvotes: 0

Views: 62

Answers (2)

Cato
Cato

Reputation: 3701

Try something like 

SELECT 
    Q1.id, Q1.cat, 
    MIN(Q1.date) AS min_dat, 
    MAX(Q1.date) AS max_dat 
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (PARTITION BY id, cat ORDER BY [date]) AS r1, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY [date]) AS r2
    ) AS Q1
GROUP BY 
    Q1.id, Q1.r2 - Q1.r1

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176214

You could use windowed MIN/MAX:

WITH cte AS (
  SELECT *,SUM(CASE WHEN row_num > 1 THEN 0 ELSE 1 END) 
         OVER(PARTITION BY id, cat ORDER BY date_col) AS grp
  FROM tab
)
SELECT *, MIN(date_col) OVER(PARTITION BY id, cat, grp) AS min_date,
          MAX(date_col) OVER(PARTITION BY id, cat, grp) AS max_date
FROM cte
ORDER BY id, date_col, cat;

Rextester Demo

Upvotes: 2

Related Questions