Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-07 : 05:38:09

Hi experts,
create table room
(
roomid int primary key,
lookupdesc varchar(10),
fromdate datetime,
todate datetime
)
go

insert into room
select 1,'single','2011-04-03','2011-04-08'union all
select 2,'Double','2011-04-03','2011-04-08'

--==============
create table subcustlink
(
id int identity,
roomid int references room(roomid),
subcustId int,
fromdate datetime,
todate datetime
)
go
insert into subcustlink(roomid,subcustId,fromdate,todate)
select 1,2,'2011-04-05','2011-04-08'

select * from room
select * from subcustlink

Required output:
for single roomid if fromdate-todate=5 days
output will be 6rows(dates(fromdate,middle4dates,enddate))

roomid lookupdesc subcustId date
1 single null 2011-04-03
1 single null 2011-04-04
1 single 2 2011-04-05
1 single 2 2011-04-06
1 single 2 2011-04-07
1 single 2 2011-04-08

Could you please help me how can i get Result in above format

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-07 : 06:29:38

I did it using cte

with cte as
(
select roomid,lookupdesc,fromdate from room
union all
select c.roomid,c.lookupdesc,c.fromdate+1 from cte c join room r on r.roomid=c.roomid where r.todate>c.fromdate
)
select
c.roomid
,c.lookupdesc
,c.fromdate as [date]
,subcustId
from cte c
left join subcustlink l on l.roomid=c.roomid
where c.fromdate between l.fromdate and l.todate
union all
select
c.roomid
,c.lookupdesc
,c.fromdate as [date]
,null as subcustId from cte c
join subcustlink l on l.roomid=c.roomid
where c.fromdate not between l.fromdate and l.todate

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources