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.
9条答案
按热度按时间qltillow1#
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.
ufj5ltwl2#
Here you go..
t98cgbkg3#
I have inserted these values into a table called
#consec
and then perforemed the following:As long as your date periods are not overlapping, that should do the job for you.
6xfqseft4#
Here it is my sample with test data:
Replace #tmp data with your actual table.
q1qsirdb5#
You can do like this and here is the sqlfiddle
fcipmucu6#
Another simple solution that could work here is -
uhry853o7#
--MS SQL
--Oracle
az31mfrm8#
with cte as( select start_date, end_date, start_date - row_number() over (order by start_date) as GRN from projects)
select min(start_date), max(end_date) from cte
group by grn order by count(grn),min(start_date)
cfh9epnr9#