Hi,
I’m not sure whether a “Foreach” capability is achievable in SQL; I’ve examined joins, unions and subqueries but can’t come up with a solution.
Scenario is 3 related tables: Location, Room, Booking (date and guest name) and what I wish to do is produce a data grid that shows every for each Location and for each room the status on a given day as follows:
LocationRoomDateGuest
Main11/1/06Jones
Main21/1/06Smith
Main3nullnull
Annex11/1/06Bloggs
House1nullnull
In psuedo-code what I want to do is
For a given date
Show each location
for each location show each room
for each room show guest (if present) else show null.
I can generate Location and Room OK using LEFT OUTER JOIN but when I introduce the date check it all goes pear shaped in that (obviously) the grid is not populated with nulls in place of the non-existent booking records!
Craighton:
Is this the idea of it?
|||set nocount on
declare @.location table( location varchar (20) not null)
insert into @.location values ('Main')
insert into @.location values ('Annex')
insert into @.location values ('House')
insert into @.location values ('Planned')
--select * from @.locationdeclare @.room table ( location varchar (20) not null, room varchar (8) not null )
insert into @.room values ('Main', '1')
insert into @.room values ('Main', '2')
insert into @.room values ('Main', '3')
insert into @.room values ('Annex', '1')
insert into @.room values ('Annex', '2')
insert into @.room values ('House', '1')
--select * from @.roomdeclare @.booking table
( location varchar (20) not null,
room varchar (8) not null,
booking datetime not null,
guest varchar (20) null
)
insert into @.booking values ('Main', '1', '1/1/6', 'Jones')
insert into @.booking values ('Main', '2', '1/1/6', 'Smith')
insert into @.booking values ('Annex', '1', '1/1/6', 'Bloggs')
--select * from @.bookingselect a.location,
b.room,
c.booking,
c.guest
from @.location a
full join @.room b
on a.location = b.location
full join @.booking c
on ( a.location = c.location or
b.location = c.location
)
and ( b.room = c.room )
-- -
-- S A M P L E O U T P U T :
-- --- location room booking guest
-- -- -- --
-- Main 1 2006-01-01 00:00:00.000 Jones
-- Main 2 2006-01-01 00:00:00.000 Smith
-- Main 3 NULL NULL
-- Annex 1 2006-01-01 00:00:00.000 Bloggs
-- Annex 2 NULL NULL
-- House 1 NULL NULL
-- Planned NULL NULL NULL
I forgot the "for a given date part." You will need to add:
|||what about using a cross apply in place of the join ?|||and c.booking = @.givenDate
You can do the following (assumes that only one guest can stay in a room):
select l.Location, r.Room, @.date as Date
, (select b.Guest from Booking as b where b.RoomId = r.RoomId and b.Date = @.date) as Guest
from Location as l
join Room as r
on r.LocationId = r.LocationId
I might have made some incorrect assumptions about your schema and relationships between the tables. But you should get the idea. You don't need to do outer joins. Also, if you need to do this for a range of dates then use a Calendar table and cross join with that like:
select l.Location, r.Room, c.dt as Date
, (select b.Guest from Booking as b where b.RoomId = r.RoomId and b.Date = c.dt) as Guest
from Location as l
join Room as r
on r.LocationId = r.LocationId
cross join Calendar as c
where c.dt >= @.date1 and c.dt < dateadd(week, 7, @.date1)
|||Mugambo,
Thank you for this - it worked well until the date check was added! See below for the solution from Umachandar. again, thanks for your response.
|||Carllop,
Thanks for this - Iused both solution from Umachander and one contained the (new to me!) Cross feature.
|||Umachandar,
Many thanks - worked a treat!
