CODEWITHSUNDEEP
sqlsql-servert-sql
eldrior
eldrior

Reputation: 75

TSQL adding missing time "blocks"

I have a program where a user creates certain time blocks similar to appointments in Outlook. The result is a table like the following (full Date, I shortened it to time in this example)

DATE_START    DATE_END    COMMENT
01:00         03:00       some comment
05:00         07:00       some comment
12:00         15:00       some comment

I need to generate the time blocks that are empty right now so the table looks like this after:

DATE_START    DATE_END    COMMENT
01:00         03:00       some comment
03:00         05:00       dummy             <--
05:00         07:00       some comment
07:00         12:00       dummy             <--
12:00         15:00       some comment

This should run as a transact TSQL statement once a day. My problem is, that I don't know how to compare two rows to generate the new lines.. Any advice?

Edit: We are using SQL Server 2008

Thank you in advance

Upvotes: 1

Views: 39

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

This is ugly ,but this works for any version..

create table appointments
(
dtstart int,
dtend int ,
cmnts char(40)
)

insert into appointments

select 1,3,'dd'
union all
select 5,7 ,'dd'
union all
select 12,15,'cc'


with cte
as
(
select *,row_number() over (order by (Select null)) as rn 
from appointments 
)
select 
dummy from
(
select  distinct b.dummy,rn
from cte t1
cross apply
(select case when t2.dtstart>t1.dtend  and t1.rn+1=t2.rn then cast(t1.dtend as varchar)+'-'+cast(t2.dtstart as varchar)
 else cast(t1.dtstart as varchar)+'-'+cast(t1.dtend as varchar) end 'dummy'
from cte t2) b
) b
 order by rn

Output:

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do this using lead():

select t.date_end, t.next_date_start, 'dummy**'
from (select t.*, 
             lead(date_start) over (order by date_start) as next_date_start
      from t
     ) t
where next_date_start <> date_end;

Before SQL Server 2012, lead() is not available but you can use the similar logic via apply.

Upvotes: 1

Related Questions