looping through date ranges that straddle previous start date range
22 Репутация автора
I have a range of dates (start and end dates) as below:
| StartDate | EndDate | | -----------| -----------| | 2016-09-03 | 2016-09-07 | | 2016-09-02 | 2016-09-08 | | 2016-09-04 | 2016-09-06 | | 2016-09-01 | 2016-09-06 | | 2016-08-31 | 2016-09-02 | | 2016-08-11 | 2016-08-14 | | 2016-08-02 | 2016-08-03
I need to start on the date range with the maximum EndDate that straddles 2016-09-05 which is set in stone. In this case it is 2016-09-02 to 2016-09-08. From this date range I use the StartDate as my new straddle date, i.e. 2016-09-02. I now need to find the maximum EndDate that straddles 2016-09-02 which should be 2016-09-01 to 2016-09-06. Again the StartDate becomes my new straddle date, i.e. 2016-09-01 and so there is one more date range that straddles this which is 2016-08-31 to 2016-09-02. My final straddle date becomes 2016-08-31 as there are no more dates that straddle this date.
This is a simple example which could contain many more dates which either straddle the previous date range or don't straddle at all and therefore should be ignored.
How do I do this in SQL Server? Is it possible?Автор: Fred_Scuttle Источник Размещён: 22.08.2016 09:18
2576 Репутация автора
Автор: Anton Размещён: 22.08.2016 11:34
create table #temp (dt1 date, dt2 date) .... declare @dt_new date = '2016-09-05', @dt_old date while (@dt_new is not null) begin set @dt_old = @dt_new set @dt_new = null select top 1 @dt_new = dt1 from #temp where dt1 < @dt_old and @dt_old < dt2 order by dt2 desc end select @dt_old