Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of items which run sequentially by date with both a start and end for each entry:

ID | Start            | End
----------------------------------------
1  | 2012-08-20 00:00 | 2012-08-20 23:59 
2  | 2012-08-21 00:00 | 2012-08-21 23:59 
3  | 2012-08-22 00:00 | 2012-08-22 23:59 
4  | 2012-08-23 00:00 | 2012-08-23 23:59 
5  | 2012-08-24 00:00 | 2012-08-24 23:59 
6  | 2012-08-28 00:00 | 2012-08-28 23:59 
7  | 2012-08-29 00:00 | 2012-08-29 23:59 
8  | 2012-08-30 00:00 | 2012-08-30 23:59

Note that there are gaps in the data (for a weekend and a bank holiday). Is it possible to write a query which selects the records at the edges of the gaps? What I want is a general purpose way to end up with something like this:

ID | Start            | End
----------------------------------------
1  | 2012-08-20 09:00 | 2012-08-20 23:59 
2  | 2012-08-21 00:00 | 2012-08-21 23:59 
3  | 2012-08-22 00:00 | 2012-08-22 23:59 
4  | 2012-08-23 00:00 | 2012-08-23 23:59 
5  | 2012-08-24 00:00 | 2012-08-24 17:00 
6  | 2012-08-28 09:00 | 2012-08-28 23:59 
7  | 2012-08-29 00:00 | 2012-08-29 23:59 
8  | 2012-08-30 00:00 | 2012-08-30 17:00

Note that the start time of 1 and 6 has been updated, as has the end time of 5 and 8.

In the real database I am forced to used this day by day data layout as it has to retain compatibility with a legacy client, so I can't simply insert the ranges directly. Also in the real data I could be dealing with several hundred of these records at a time with multiple gaps.

The target platform is SQL2005/2008, though bonus points for solutions which work in SQL2000 compatibility mode.

share|improve this question
add comment

1 Answer

up vote 7 down vote accepted

You can use outer apply the get the lead and lag values and in the field list you can use datediff to see if the gap is something other than one day.

select T1.ID,
       case
         when datediff(day, Lag.Start, T1.Start) = 1 then T1.Start
         else dateadd(hour, 9, T1.Start)
       end as Start,
       case
         when datediff(day, T1.Start, Lead.Start) = 1 then T1.[End]
         else dateadd(hour, 17, T1.Start)
       end as [End]
from YourTable as T1
  outer apply
    (
      select top(1) T2.Start
      from YourTable as T2
      where T2.Start < T1.Start
      order by T2.Start desc
    ) as Lag(Start)
  outer apply
    (
      select top(1) T2.Start
      from YourTable as T2
      where T2.Start > T1.Start
      order by T2.Start
    ) as Lead(Start)

The query above works from SQL Server 2005 even with compatibility SQL Server 2000 (80).

If you find your way to SQL Server 2012 you can use lead and lag instead of simulating with an outer apply.

select T1.ID,
       case
         when datediff(day, lag(T1.Start) over(order by T1.Start), T1.Start) = 1 
         then T1.Start
         else dateadd(hour, 9, T1.Start)
       end as Start,
       case
         when datediff(day, T1.Start, lead(T1.Start) over(order by T1.Start)) = 1 
         then T1.[End]
         else dateadd(hour, 17, T1.Start)
       end as [End]
from YourTable as T1

SQL Fiddle

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.