I want to fill the calendar object which requires start and end date information. I have one column which contains a sequence of dates. Some of the dates are consecutive (have one day difference) and some are not.
InfoDate 2013-12-04 consecutive date [StartDate] 2013-12-05 consecutive date 2013-12-06 consecutive date [EndDate] 2013-12-09 [startDate] 2013-12-10 [EndDate] 2014-01-01 [startDate] 2014-01-02 2014-01-03 [EndDate] 2014-01-06 [startDate] 2014-01-07 [EndDate] 2014-01-29 [startDate] 2014-01-30 2014-01-31 [EndDate] 2014-02-03 [startDate] 2014-02-04 [EndDate]
I want to pick each consecutive dates range’s start and end date (the first one and the last one in the block).
StartDate EndDate 2013-12-04 2013-12-06 2013-12-09 2013-12-10 2014-01-01 2014-01-03 2014-01-06 2014-01-07 2014-01-29 2014-01-31 2014-02-03 2014-02-04
I want to solve the problem using SQL only.
No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.
WITH t AS ( SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i FROM @d GROUP BY InfoDate ) SELECT MIN(d),MAX(d) FROM t GROUP BY DATEDIFF(day,i,d)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With