Foreach?

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 @.location

declare @.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 @.room

declare @.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 @.booking

select 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:

and c.booking = @.givenDate

|||what about using a cross apply in place of the join ?|||

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!