Query help

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  April 7, 2011 at 3:31 am

  #237462

  Hi experts,

  here is the sample data

  create table room

  (

  roomidint primary key,

  lookupdesc varchar(10),

  fromdatedatetime,

  todatedatetime

  )

  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

  (

  idint identity,

  roomidint references room(roomid),

  subcustIdint,

  fromdatedatetime,

  todatedatetime

  )

  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))

  roomidlookupdescsubcustId date

  1singlenull2011-04-03

  1singlenull2011-04-04

  1single22011-04-05

  1single22011-04-06

  1single22011-04-07

  1single22011-04-08

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  April 7, 2011 at 4:50 am

  #1308405

  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