CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex
Alex

Reputation: 85

SQL query to find local max, min of date based data

I have the following data set

Date         Category  
2014-01-01     A  
2014-01-02     A  
2014-01-03     A  
2014-01-04     B  
2014-01-05     B  
...  
2014-01-10     B  
2014-01-11     A  
...  
2014-01-20     A

The result I want to achieve is to find local min/max date for A and B, so as follows: 

MinDate       MaxDate       Category  
2014-01-01    2014-01-03       A  
2014-01-04    2014-01-10       B  
2014-01-11    2014-01-20       A

Note: using 

Select min(date), max(date), category from TABLE Group by category

will create the result 

MinDate MaxDate Category  
2014-01-01 2014-01-20 A  
2014-01-04 2014-01-10 B

this is not what I want to achieve

Upvotes: 2

Views: 1382

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Assuming you have a DBMS that supports window functions you can do this:

select category, grp, min(date) as min_date, max(date) as max_date
from (
    select category, date
         , row_number() over (order by date) 
         - row_number() over (partition by category order by date) as grp
    from T
) as X
group by category, grp
order by min(date)

The idea is to order all rows according to date and to order all rows in each category according to date. If the difference between these numbers changes it means that the chain of consecutive events for an category is broken by another category.

Upvotes: 5

Related Questions