Вопрос:

looping through date ranges that straddle previous start date range

sql-server

49 просмотра

1 ответ

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

Ответы (1)


1 плюс

2576 Репутация автора

Решение
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
Автор: Anton Размещён: 22.08.2016 11:34
Вопросы из категории :
32x32