How to get a Table from SQL Server showing the Schedule for the whole day of a Room?

Question

I need a query that bring me a table like

7 AM | 7:30 AM | 8 AM   | 8:30 AM...........|6:30 PM|
----- --------- -------- ----- ............. -------
     | Booked  | Booked | Booked ...........|       |

My Table Name is : zUSM_RoomReservationInfo This is the structure and Data of the Table

ReservationID RoomId  StartDateTime           EndDateTime             
------------- ------- ----------------------- ----------------------- 
1             102     2013-02-01 10:00:00.000 2013-02-01 12:00:00.000 
2             201     2013-01-27 16:00:00.000 2013-01-27 17:00:00.000 
3             102     2013-02-01 14:00:00.000 2013-02-01 16:00:00.000

By Giving ONE Date and Room Id as Input it should give me table like I mentioned above.

Any help here: http://stackoverflow.com/questions/6111263/sql-scheduling-select-all-rooms-available-for-given-date-range ? — Morphed, Feb 04 '13 at 11:16
@Paddy No Help there.. I need output like mentioned in the question — Fahad Hussain, Feb 04 '13 at 11:25

TechDo · Accepted Answer · 2013-02-05T05:10:01.493

1

Please try:

DECLARE @RoomID NVARCHAR(10), @DtParam NVARCHAR(20), @dt1 DATETIME, @dt2 DATETIME, @Col NVARCHAR(MAX), @Query NVARCHAR(MAX)
select @RoomID='102', @DtParam='01-Feb-2013', @dt1='07:00', @dt2='17:00', @Col='', @Query=''

CREATE TABLE #table (DT DATETIME)

while @dt1<=@dt2
begin
    SET @Col=@Col+'['+CONVERT(CHAR(5), @dt1, 108)+'],'
    insert into #table values (@dt1)
    SET @dt1=dateadd(minute, 30, @dt1)
end

select @Col=LEFT(@Col, LEN(@Col) -1)

set @Query='select '+@Col+' From(
select DT, ReservationID From #table t1 left outer join (select * from zUSM_RoomReservationInfo
where RoomID='+@RoomID+' AND DATEADD(dd, 0, DATEDIFF(dd, 0, StartDateTime))=CONVERT(DATETIME, '''+@DtParam+'''))t2 on t1.DT
between CONVERT(CHAR(5), StartDateTime, 108) and CONVERT(CHAR(5), EndDateTime, 108)

)x pivot (count(ReservationID) for DT IN ('+  @Col+'))PIV'
exec (@Query)

drop table #table

edited Feb 05 '13 at 05:10

answered Feb 04 '13 at 12:16

TechDo

18,398
3
51
64

This is Very Near to My Requirement.. :) but I don't need the information of table "zUSM_RoomReservationInfo" in results, and this query is showing two records if the room has two bookings in the same day.. I want them to be in One Row. – Fahad Hussain Feb 04 '13 at 12:36
1

You can add where condition for checking the date, please check the edited answer. – TechDo Feb 04 '13 at 12:52
Bro I am getting two rows. I want to have it in one row – Fahad Hussain Feb 04 '13 at 13:40
1

Yes, u r correct, I haven't noticed that.. plz check the edited answer. – TechDo Feb 05 '13 at 05:10

How to get a Table from SQL Server showing the Schedule for the whole day of a Room?

1 Answers1