CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
Limatons
Limatons

Reputation: 13

Select periods from SQL Server

I have a table that contains datetime and flag, something like this:

2014-11-06 | True
2014-11-07 | True
2014-11-08 | True
2014-11-09 | False
2014-11-10 | False
2014-11-11 | True
2014-11-12 | True
2014-11-13 | False
2014-11-14 | True
2014-11-15 | False

I need help with a SQL query that returns min and max date for each period of True, like this:

2014-11-06 | 2014-11-08
2014-11-11 | 2014-11-12
2014-11-14 | 2014-11-14

Please, help. It could be a LinqToSql query or T-SQL. Thanks

Upvotes: 1

Views: 107

Answers (2)

Sagar
Sagar

Reputation: 275

I like below approach but if you have a missing date in your dataset then it won't work. I tried this, may be useful for someone who has such dataset.

Select Min(dt), Max(dt) 
from (Select dt, r_num, 
            (r_num - row_number() over(order by r_num)) as grp
      From ( select dt,flag, 
                    row_number() over(Order by dt) r_num
              From table t ) x
      Where flag = 'true'
     ) t
group by grp

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Sequential periods of "true" have a nice property. If you subtract an increasing sequence of numbers from the dates, everything in a group wil have the same date. You can apply this logic:

select min(dt), max(dt)
from (select dt,
             (dt - row_number() over (order by dt) ) as grp
      from table t
      where flag = true
     ) t
group by grp;

Upvotes: 3

Related Questions