September 17, 2015 at 4:01 am
Hi,
I'm new to SQL and am facing a query which I don't know how to write SQL for...
I need to display at what hours rooms are booked in each room
My Input data is as follows:
Description StartDateTime EndDateTime RoomName
Exam 01/12/2004 11:00:00 01/12/2004 12:00:00 AB0.01
Test 01/12/2004 14:00:00 01/12/2004 17:00:00 AB0.01
Meeting 01/12/2004 10:00:00 01/12/2004 13:30:00 AB0.05
I need the output to be displayed in an SSRS report to show the following:
Chosen Date
01/12/2004 RoomName
AB/01 Hour Description
12 am
1 am
2 am
3 am
4 am
5 am
etc etc
11 am Exam
12 pm Exam
1 pm
2 pm Test
3 pm Test
4 pm Test
5 pm Test
-----
11PM
I must have every single hour listed whether it is booked or not.
Can anyone please help? Is this possible?
September 17, 2015 at 4:11 am
It's possible.
You need to generate a list with all the hours for the date you picked.
This is basically a tally table.
To make sure you have all rooms (for example if a room has absolutely no bookings for an entire day), you can cross join this hour list with the list of rooms.
Then you join this list against your input using a LEFT JOIN.
Something like
allHoursAndRooms a
LEFT JOIN myTable t ON a.Room = t.Room AND a.hour BETWEEN StartDatetime and EndDateTime
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 17, 2015 at 4:35 am
I already have done a query to link 4 tables to produce the four fields I need:
Description
StartDateTime
EndDateTime
Room Name
Being new to sql, how do I add the hour tally and then perform the other joins you mentioned?
Here is what I have in Query Designer
SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime, data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id
This query produces this:
Exam 01/12/2004 11:00:00 01/12/2004 12:00:00 AB0.01
Test 01/12/2004 14:00:00 01/12/2004 17:00:00 AB0.01
Meeting 01/12/2004 10:00:00 01/12/2004 13:30:00 AB0.05
Can you please help me add the tally hours (in one column) and then work out how to put the Description (i.e. Booking name) in the next column. Obviously some bookings will be more than 1 hour long, so need to include it in multiple hour slots....
Happy to provide any more info, it's taken me ages to work out just how to get the data in the first place.
Kind Regards and thanks in advance for any help with this
September 17, 2015 at 10:29 am
what do you want to return when a meeting spans over an hour segment...say for example 9.30am to 10.30am
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 17, 2015 at 10:32 am
rkelly58 (9/17/2015)
Hi,I'm new to SQL and am facing a query which I don't know how to write SQL for...
I need to display at what hours rooms are booked in each room
My Input data is as follows:
Description StartDateTime EndDateTime RoomName
Exam 01/12/2004 11:00:00 01/12/2004 12:00:00 AB0.01
Test 01/12/2004 14:00:00 01/12/2004 17:00:00 AB0.01
Meeting 01/12/2004 10:00:00 01/12/2004 13:30:00 AB0.05
I need the output to be displayed in an SSRS report to show the following:
Chosen Date
01/12/2004 RoomName
AB/01 Hour Description
12 am
1 am
2 am
3 am
4 am
5 am
etc etc
11 am Exam
12 pm Exam
1 pm
2 pm Test
3 pm Test
4 pm Test
5 pm Test
-----
11PM
I must have every single hour listed whether it is booked or not.
Can anyone please help? Is this possible?
why is 12pm showing as "Exam"...this is the finish time ???
likewise for 5pm "Test
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 17, 2015 at 11:16 am
You are totally right, my mistake, if the exam finishes at 5, then I obviously don't want to include that in the next hour, doh
If the booking begins/finishes at 10:30 for example, we can just count it as that hour, just to keep it simple.
e.g. Exam start 10:30 finish 11:30
10 exam
11 exam
unless there was a way of splitting it down by half hour slots which would make it super accurate???
Thank you
September 17, 2015 at 3:19 pm
rkelly58 (9/17/2015)
You are totally right, my mistake, if the exam finishes at 5, then I obviously don't want to include that in the next hour, dohIf the booking begins/finishes at 10:30 for example, we can just count it as that hour, just to keep it simple.
e.g. Exam start 10:30 finish 11:30
10 exam
11 exam
unless there was a way of splitting it down by half hour slots which would make it super accurate???
Thank you
It can be accurate to 3.333 milliseconds. How much precision do you need, and how are you storing the dates/times (it looks like a DATETIME data type, but can you verify this)?
Since you're new, I'll advise you to read the link in my signature about how to post your questions. Then create the DDL (preferable into a temp table), and the insert statements for your sample data that you need. This will make it so much easier for one of the volunteers here (yep, we don't get paid for this) to run with what you are trying, without having to spend time doing this for you (many will just skip your question if you didn't do this).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 17, 2015 at 3:58 pm
a few more questions if I may please......
I appreciate that you are new here so:
I recall seeing similar questions at this time of year when a new semester starts.....is this homework?
to help us help you....please read http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ and understand and follow the guide so that you can help us help you ... by you providing table/data set up scripts that we can easily copy into SSMS to replicate your sample data
Moving on......
from the way I read your post, you are only interested in returning a single set of data for 1 specific day (24 hourly rows) for a specific room number...is this correct?
what are you expecting when a booking spans more than one day...this maybe a late night session or a block booking for two or more days
can you guarantee that there will never be any overlapping bookings?
your initial query has 4 tables
data.V_ACTIVITY
data.V_ACTIVITY_DATETIME
data.V_ACTIVITY_LOCATION
data.V_LOCATION
are these actually separate tables or views based on a table?
you made the following response to a previous question
You are totally right, my mistake, if the exam finishes at 5, then I obviously don't want to include that in the next hour, doh
If the booking begins/finishes at 10:30 for example, we can just count it as that hour, just to keep it simple.
e.g. Exam start 10:30 finish 11:30
10 exam
11 exam
unless there was a way of splitting it down by half hour slots which would make it super accurate???
I wouldnt say that splitting into half hour slots is "super accurate"...would you?
I think you need to determine with the business the level of granularity required....be that 1 hr / ½ hour / ¼ hour...once that is agreed we can proceed with some code
what does the business really want to see?.......have you asked them?
for example....if I was using your report, then potentially as an end user my question maybe is..show all available time slots for all rooms on a specific day...in which case we have to include all rooms all hours as Koen previously suggested.
Or... show me all available time slots for Room XXX in the next seven days (maybe room xxx is the only one with seating for 20+ people)
...and so on.
sorry for sounding pedantic, but this type of question has a habit of the OP constantly adding more detail/requests to refine the output .....mainly due to not fully understanding / defining what is required at the outset...I call this "drip feeding".....and its frustrating to say the least.
I am not saying that this will be the case in your posts, and I am not criticising you personally....its just a form of self preservation...."once bitten......as they say";-)
edit...here is some sample data...maybe you can tell us what your expected results are
CREATE TABLE #inputtable(
meeting_type VARCHAR(20) NOT NULL
,StartDateTime DATETIME NOT NULL
,EndDateTime DATETIME NOT NULL
,RoomName VARCHAR(6) NOT NULL
);
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('previousnight','2004-12-03 22:00:00.000','2004-12-04 02:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('1hr','2004-12-04 06:00:00.000','2004-12-04 07:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('split','2004-12-04 09:30:00.000','2004-12-04 10:30:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('3hr','2004-12-04 12:00:00.000','2004-12-04 15:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('bactoback1','2004-12-04 17:00:00.000','2004-12-04 18:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('backtoback2','2004-12-04 18:00:00.000','2004-12-04 19:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('nextnight','2004-12-04 22:00:00.000','2004-12-05 01:00:00.000','AB0.05');
SELECT * FROM #inputtable
DROP TABLE #inputtable
Best wishes
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2015 at 4:02 am
Hi,
Firstly my sincere apologies for not posting according to the guide, I hadn't realized when joining that you guys would response, let alone help me with this, so i'm truly thankful.
Ok to answer your questions, here goes:
1) This isn't homework, this is a new area I've started to delve into as part of my job and have been asked to look into doing a Reporting Services report on Room bookings by hour for either a day or a specific date range. I only asked here about bookings by "day" as I thought it would be easier to ask for help on one part.
2) Yes i want the user to pick a specific Room and then show all bookings by hour for that day
So parameters are:
Site -> Building -> Room
3) All Rooms have unique room numbers and are pulled from a Table. I use a dropdown list which is populated from a Dropdown Table using a query, which the user picks.
4) Yes to 24 hourly rows for a specific room number and display any bookings
5) Luckily for me, block bookings (using my query) pulls individual bookings for block bookings (phew)
So if Meeting x is booked for 3 days 2-4pm, if appears in my query results are 3 separate dates with same times. Therefore i've checked and there is no overlapping meetings for each room.
6) data.V_ACTIVITY
data.V_ACTIVITY_DATETIME
data.V_ACTIVITY_LOCATION
data.V_LOCATION
The above 4 are all VIEWS
7) From the sample data you posted, i'd expect the report to look like this...
Row 1 previousnight would not happen, all the data bookings appear within the same day
For the rest... Output is
Date -> Room Name AB0.05
Time Booking
12 am
1 am
2 am
3 am
4 am
5 am
6 am 1hr
7 am
8 am
9 am split
10 am split
11 am
12 pm 3hr
1 pm 3hr
2 pm 3hr
3 pm
4 pm
5 pm backtoback1
6 pm backtoback2
7 pm
8 pm
9 pm
10 pm
11 pm
Not sure how you would deal with half hour starts and finishes, but could be add something to the Booking that says, it started at 10:30am or ended at 11:30am for example
9am split (starts at 9:30)
10am split (ends at 10:30
That way the user at least can tell that the books weren't starting and ending on the hour?
8) This is part of a bigger project to show PCs login by hour and then showing any room bookings in that hour. I've been asked to show by date range within a specific Room, how many PC were logged in per hour and if any Room Bookings were done, so we get a picture of bookings vs PCS used.... This is a wider project, and i'm working on the PC logins bit separately using another dataset.
The PC logins and Room Bookings datasets are on separate Servers and databases, so down the line i will need to somehow combined the results. But as i'm new to all this, its little steps at a time for me. Hope that makes sense.
At the moment, if I can just get a way of the User inputting the Site, Building and Room, then producing a 24 hourly rows display and if there are any bookings in any of those hours, that would be a great start.
I could list all the stuff I've done on the PC logins part, but i'm wondering if it's easier to work on just the room booking part first or in your expert opinion i should just post everything?
Happy to provide any more info as required.
P.S. I don't want to rub up any of you kind helpers the wrong way, so apologies for any offence caused.
Any help is appreciated.
Thank You
September 18, 2015 at 4:24 am
rkelly58 (9/18/2015)
Hi,Firstly my sincere apologies for not posting according to the guide, I hadn't realized when joining that you guys would response, let alone help me with this, so i'm truly thankful.
Ok to answer your questions, here goes:
1) This isn't homework, this is a new area I've started to delve into as part of my job and have been asked to look into doing a Reporting Services report on Room bookings by hour for either a day or a specific date range. I only asked here about bookings by "day" as I thought it would be easier to ask for help on one part.
2) Yes i want the user to pick a specific Room and then show all bookings by hour for that day
So parameters are:
Site -> Building -> Room
3) All Rooms have unique room numbers and are pulled from a Table. I use a dropdown list which is populated from a Dropdown Table using a query, which the user picks.
4) Yes to 24 hourly rows for a specific room number and display any bookings
5) Luckily for me, block bookings (using my query) pulls individual bookings for block bookings (phew)
So if Meeting x is booked for 3 days 2-4pm, if appears in my query results are 3 separate dates with same times. Therefore i've checked and there is no overlapping meetings for each room.
6) data.V_ACTIVITY
data.V_ACTIVITY_DATETIME
data.V_ACTIVITY_LOCATION
data.V_LOCATION
The above 4 are all VIEWS
7) This is part of a bigger project to show PCs login by hour and then showing any room bookings in that hour. I've been asked to show by date range within a specific Room, how many PC were logged in per hour and if any Room Bookings were done, so we get a picture of bookings vs PCS used.... This is a wider project, and i'm working on the PC logins bit separately using another dataset.
The PC logins and Room Bookings datasets are on separate Servers and databases, so down the line i will need to somehow combined the results. But as i'm new to all this, its little steps at a time for me. Hope that makes sense.
At the moment, if I can just get a way of the User inputting the Site, Building and Room, then producing a 24 hourly rows display and if there are any bookings in any of those hours, that would be a great start.
I could list all the stuff I've done on the PC logins part, but i'm wondering if it's easier to work on just the room booking part first or in your expert opinion i should just post everything?
Happy to provide any more info as required.
P.S. I don't want to rub up any of you kind helpers the wrong way, so apologies for any offence caused.
Any help is appreciated.
Thank You
many thanks for providing more of a back story.....maybe the following will give you a start
CREATE TABLE #roomhours(
hour_number INTEGER NOT NULL PRIMARY KEY
,hour_description VARCHAR(5) NOT NULL
);
INSERT INTO #roomhours(hour_number,hour_description) VALUES (0,'12 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (1,'1 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (2,'2 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (3,'3 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (4,'4 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (5,'5 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (6,'6 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (7,'7 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (8,'8 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (9,'9 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (10,'10 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (11,'11 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (12,'12 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (13,'1 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (14,'2 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (15,'3 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (16,'4 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (17,'5 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (18,'6 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (19,'7 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (20,'8 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (21,'9 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (22,'10 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (23,'11 pm');
CREATE TABLE #inputtable(
meeting_type VARCHAR(20) NOT NULL
,StartDateTime DATETIME NOT NULL
,EndDateTime DATETIME NOT NULL
,RoomName VARCHAR(6) NOT NULL
);
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('1hr','2004-12-04 06:00:00.000','2004-12-04 07:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('split','2004-12-04 09:30:00.000','2004-12-04 10:30:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('3hr','2004-12-04 12:00:00.000','2004-12-04 15:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('bactoback1','2004-12-04 17:00:00.000','2004-12-04 18:00:00.000','AB0.05');
INSERT INTO #inputtable(meeting_type,StartDateTime,EndDateTime,RoomName) VALUES ('backtoback2','2004-12-04 18:00:00.000','2004-12-04 19:00:00.000','AB0.05');
SELECT
r.hour_description
, i.Meeting_type
, i.StartDateTime
, i.EndDateTime
, i.RoomName
FROM #roomhours AS r LEFT OUTER JOIN
#inputtable i ON r.hour_number >= datepart(hh,i.StartDateTime)
and r.hour_number < datepart(hh,i.EndDateTime);
DROP TABLE #roomhours;
DROP TABLE #inputtable;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2015 at 4:27 am
P.S. i;ve added a bit more info to my above post
September 18, 2015 at 7:33 am
rkelly58 (9/18/2015)
P.S. i;ve added a bit more info to my above post
yeah..I see that
how did you get on with the code I gave you?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2015 at 8:13 am
Hi J,
I tried your code and the results are exactly like I want. -You make that look so easy!!!!
How do I add your query to my existing query to use my data instead of the made up data you added to the query?
Cheers,
September 18, 2015 at 8:49 am
rkelly58 (9/18/2015)
Hi J,I tried your code and the results are exactly like I want. -You make that look so easy!!!!
How do I add your query to my existing query to use my data instead of the made up data you added to the query?
Cheers,
is the code you are speaking of ?
SELECT
data.V_ACTIVITY.Description
, data.V_ACTIVITY_DATETIME.StartDateTime
, data.V_ACTIVITY_DATETIME.EndDateTime
, data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID
INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId
INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id;
i dont see any WHERE clause that is going to limit the rows........how many rows does this return when run...?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2015 at 9:05 am
Hi,
Yes that's the code.
It pulls out 140,000 rows. -This is data with bookings for "all" rooms
On the Reporting Services, I have parameters called StartDate (this is the date the user wants to check) and a hidden EndDate(this just adds 1 to the date, so that only one day can be selected). Then there is a Site Parameter and a Building Parameter and Room Parameter.
If we can include these in the query, then the results will be filtered to that particular room.
The only slight issue is that the Room name in the Dropdown parameter is named slightly different from the Room Name in the V_LOCATION.Name column.
So for example the dropdown Room name says AB0.001, but the Roomname in V_LOCATION.Name is called AB0.001 Phys Lab, so when filtering the Room name, is it possible to do a LIKE/PATTERN where V_LOCATION.Name IS LIKE/PATTERN matches the Dropdown Roomname Parameter?
I've checked V_LOCATION.Name, all the Room names are the same as the Dropdown Roomname Paramter but some as above include some extra text in there after the initial room name in the same column.... Don't ask me why, it's only something I discovered when manually comparing the two Databases.
Hope that makes sense...
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply