January 31, 2011 at 11:31 am
I need to produce some statistics from our meeting booking system:
The setup for this problem is below:
----------------------------------------------------------
declare @MeetingRoom table
(
MeetingDate SMALLDATETIME
,WeekNo INT
,RoomNo Varchar(20)
)
INSERT INTO @MeetingRoom
Values
('2011-01-03',8,'Room1'), -- Mon
('2011-01-03',8,'Room1'), -- Mon
('2011-01-04',8,'Room1'), -- Tues
('2011-01-03',8,'Room2'), -- Mon
('2011-01-04',8,'Room2'), -- Tues
('2011-01-05',8,'Room2'), -- Weds
('2011-01-07',8,'Room3'), -- Fri
('2011-01-04',8,'Room4'), -- Tues
('2011-01-05',8,'Room4'), -- Weds
('2011-01-06',8,'Room4'), -- Thurs
('2011-01-03',8,'Room5'), -- Mon
('2011-01-05',8,'Room5') -- Weds
select * from @MeetingRoom
----------------------------------------------------------
--N.B. The same room can be booked more than once on the same day as some meetings only last a few hours, I'm only interested that the room on a particular day and not how many times.
I then need to produce two types of stats. The first shows how many consecutive days a room hasn't be used in a week. So if the same room was booked Mon & Weds for a particualar week then I'd expect to see that a room hadn't been used for 1x1 day (Tues) and 1x2days (Thur & Fri).
So to summarise the data in the above table for wk8
Room1 used Mon-Tues (not used Weds-Fri: 1x3 days)
Room2 used Mon-Weds (not used Thur-Fri: 1x2 days)
Room3 used Fri (not used Mon-Thurs: 1x4 days)
Room4 used Tues-Thurs (not used Mon & Fri: 1x1 day & 1x1 day)
Room5 used Mon & Weds (not Tues & Thurs-Fri: 1x1 day & 1x2 days)
So for the above data I'd like to the data presented like the data below:
Consecutive Days not used
WeekNo 1 2 3 4 5
8 3 2 1 1 0
9 data not provided
I then need to see how many rooms don't have bookings on a particular day
WeekNo Mon Tue Wed Thur Fri
8 2 2 2 4 4
9 data not provided
Any help on some or all of this problem would be greatly apprecaited
Many Thanks
Squidder
January 31, 2011 at 11:40 am
Start out by building a calendar table with all the dates you want to check, and which week each one is in. (I keep a 10-year calendar table handy for this kind of thing.)
Then you can easily join to that to get which days were missed per week.
Gaps would then be done by finding dates where it was used that are followed by dates where it wasn't, and vice versa, and joining those together to get first and last days of gaps. That's a simple self-join on the data you already have from the above queries.
The key to the whole thing is a calendar table. Build that, try a few things out with it, see what you can get. Ask more questions when you've gone down that route a bit, if you run into pieces that don't work out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2011 at 12:14 pm
Hi GSquare,
Thanks for your response.
I already have a calendar table as you have suggest. I simplified my problem and left this fact out of the description, which maybe I shouldn't have! So in my actual solution the WeekNo is coming from my calendar table.
Thanks
Squidder
January 31, 2011 at 1:03 pm
This is a bit more accurate set-up.
declare @MeetingRoom table
(
MeetingDate SMALLDATETIME
,RoomNo Varchar(20)
)
INSERT INTO @MeetingRoom
Values
('2011-01-03','Room1'), -- Mon
('2011-01-03','Room1'), -- Mon
('2011-01-04','Room1'), -- Tues
('2011-01-03','Room2'), -- Mon
('2011-01-04','Room2'), -- Tues
('2011-01-05','Room2'), -- Weds
('2011-01-07','Room3'), -- Fri
('2011-01-04','Room4'), -- Tues
('2011-01-05','Room4'), -- Weds
('2011-01-06','Room4'), -- Thurs
('2011-01-03','Room5'), -- Mon
('2011-01-05','Room5') -- Weds
declare @Rooms table
(
RoomNo Varchar(20)
)
INSERT INTO @Rooms
Values
('Room1'),
('Room2'),
('Room3'),
('Room4'),
('Room5')
-- Create calendar Table
declare @Calendar table
(
DateKey SMALLDATETIME
,WeekNo INT
)
INSERT INTO @Calendar
Values
('2011-01-03',8),
('2011-01-04',8),
('2011-01-05',8),
('2011-01-06',8),
('2011-01-07',8)
WITH cteRooms (DateKey, RoomNo)
AS
(
SELECT DateKey, RoomNo
FROM @Calendar CROSS JOIN @Rooms
)
SELECT *
FROM cteRooms cte
LEFT OUTER JOIN @MeetingRoom mr ON cte.DateKey=mr.MeetingDate AND cte.RoomNo=mr.RoomNo
ORDER BY cte.RoomNo, cte.DateKey
----------------------------------------------------------------------------
I've been working on this for most of the day without really getting anywhere.
How do I count the NULLS (room not used) to work out how many days/consecutive days have been missed in a week and then how to I transpose these results as in my first post.
Any help would be greatly appreciated.
Thanks
Paul
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply