Solved

Room free/busy time - simple query

Posted on 2004-09-08
21
456 Views
Last Modified: 2008-03-06
Stuck on creating a recordset for displaying free/busy time for a meeting calendar i'm working on
Using: MS SQL 2000

this particular problem involves 4 tables

locations (locationid int, locationname varchar)
meetings (meetingid int, meetingname varchar, meetingdate datetime, )
meetingitems  (itemid int, itemstarttime varchar, itemfinishtime varchar, locationid int, itemtypeid in, meetingid int)
itemtypes (itemtypeid int, itemtype varchar, notes char)


The tables Locations and Itemtypes are both really just lookup tables

The table Meetings contains information specific to the meeting

The table MeetingItems contains agenda items and start and finish times of each item which belongs to a meeting.

One meeting can have multiple agenda items.

The problem I am have is returning a recordset to return in my Coldfusion page to show a room availability (free/busy) chart, so at a glance a meeting organiser can see which rooms are available for a certain timeframe... will default to just one day.. from 8am to 8pm in 30 minute increments.

There is a total of 7 rooms

If you take a look at this link:
http://www.intranets.com/Branded/ScreenShot.asp?ssimage=/Images/apps/GroupSched_T1_SS.gif

You'll see what I mean - except in this example the meeting room name replaces the participants name.

Would be appreicated in a stored procedure whereby I can pass a date range and an optional locationid, so I can say show me roomX's availability for the next day or week etc.
0
Comment
Question by:dynabytes
  • 10
  • 9
  • 2
21 Comments
 
LVL 12

Expert Comment

by:kselvia
ID: 12013172
Can you provide some create table and insert statements with some sample data? We can do it but you can save us some work and possible errors by providing actual data.

You might look at http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20999291.html for a similar example that is essentially what you need to do.  

If you provide some sample data I'll write out the SQL for you unless someone else wants to roll-their-own or do it without testing.
0
 

Author Comment

by:dynabytes
ID: 12013682
LOCATIONS table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_meetingitems_locations]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[meetingitems] DROP CONSTRAINT FK_meetingitems_locations
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[locations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[locations]
GO

CREATE TABLE [dbo].[locations] (
      [locationid] [int] IDENTITY (1, 1) NOT NULL ,
      [locationname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [diaryviewable] [bit] NULL
) ON [PRIMARY]
GO


MEETINGITEMS table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[meetingitems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[meetingitems]
GO

CREATE TABLE [dbo].[meetingitems] (
      [itemid] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
      [itemstart] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [itemfinish] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [contact] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [locationid] [int] NULL ,
      [itemtypeid] [int] NULL ,
      [meetingid] [int] NULL
) ON [PRIMARY]
GO

MEETINGS table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_meetingitems_meetings]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[meetingitems] DROP CONSTRAINT FK_meetingitems_meetings
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[meetings]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[meetings]
GO

CREATE TABLE [dbo].[meetings] (
      [meetingid] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
      [notes] [char] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [meetingname] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [meetingcode] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [organiser] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [meetingdate] [datetime] NOT NULL ,
      [NumberofPeople] [smallint] NULL ,
      [Catering] [tinyint] NULL ,
      [Waitering] [tinyint] NULL ,
      [DoorAttendant] [tinyint] NULL ,
      [Created] [datetime] NULL ,
      [Createdby] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Modified] [datetime] NULL ,
      [Modifiedby] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Status] [tinyint] NULL ,
      [meetingvenue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [authorisedby] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [racp] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [faculty] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [specialsociety] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cateringnotes] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


ITEMTYPES table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_meetingitems_itemtypes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[meetingitems] DROP CONSTRAINT FK_meetingitems_itemtypes
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[itemtypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[itemtypes]
GO

CREATE TABLE [dbo].[itemtypes] (
      [itemtypeid] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
      [itemtype] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO



No sample data sorry. The columns are pretty self explanatory.

Mike
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12014068
I don't have the code here, but I did that already, here the pseudo-code (requires 1 working table, eventually a table variable in SQL Server 2000):

* insert 1 row per room and 30 minutes slot into the temp table (by default FREE time, value 1)
* update the table by joining to the meetings/meetingitems table(s), by room and time-slot time vs meeting start and end time (the time-slot time needs to be BETWEEN the meeting start and end time)
  -> set the room-slot to be USED 0

return the results...

Structure of the (temp) table:
ROOM_ID (refers to the room id)
SLOT_START_TIME (start time of the slot, ie 2004/09/09 08:00)
SLOT_END_TIME (end time of the slot, ie 2004/09/09 08:30)
ROOM_IS_FREE int default(1)

Be carefully with < and <= for start and endtime when checking if a meeting items is in a certain room time slot or not :-)

Hope this helps already
CHeers
0
 

Author Comment

by:dynabytes
ID: 12014163
Some code would be tops - I ain't a SQL guru - more of a web developer :)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12014418

DECLARE @SLOTS TABLE (  LOCATION_ID INT, SLOT_START DATETIME , SLOT_END DATETIME, ROOM_STATE int )
DECLARE @SLOT_START DATETIME
DECLARE @SLOT_END DATETIME

-- get the current date (with no time)
SET @SLOT_START = CONVERT(datetime, CONVERT(char(8), getdate(), 112) , 112 )

--get the last and first slot start time (8am)
SET @SLOT_START = DATE_ADD ( hour, 8, @SLOT_START )
SET @SLOT_END = DATE_ADD ( hour, 8, @SLOT_START )

-- loop the day with increments of 30 minutes
WHILE @SLOT_START < @SLOT_END
BEGIN
  -- generate the rows for all the rooms for this time slot
   INSERT INTO @SLOTS
   SELECT LOCATION_ID , @SLOT_START, DATE_ADD ( minute, 30, @SLOT_START ), 1
   FROM LOCATIONS

   SET @SLOT_START= DATE_ADD ( minute, 30, @SLOT_START )
END

-- now, update all the rows to set the FREE indicator to USED when the location is used in a meeting
UPDATE @SLOTS
SET ROOM_STATE = 0
FROM @SLOTS s
JOIN meetingitems m
-- the meeting item needs to be on the location/room of this slot
ON m.location_id = s.location_id
AND ( -- either the meeting items starts in the current slot
      m.item_start >= s.SLOT_START AND m.Item_start < s.SLOT_END  
      OR -- or it ends in the current slot
      m.item_end > s.SLOT_START AND m.item_end <= S.SLOT_END
      OR -- or it englobes the current slot
      m.item_start <= s.SLOT_START AND m.item_end >= S.SLOT_END
      )

SELECT * FROM @SLOTS

-- please note the usage of > < >= and <= in the UPDATE JOIN condition which is important to respect:
say a SLOT is defined from 10:00 to 10:30
a meeting item is defined from 9:00 to 10:00
--> this meeting item will NOT mark the slot as used


CHeers


0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12014420
PS: the above is not tested for syntax...
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12014424
Some simple

insert ITEMTYPES (itemtype) SELECT 'somekindofitem'
insert ....

to populate the tables with a few reasonable rows would help. I started to do it before I wrote back the first time but accidently closed the wrong session so...

do take a look at the link I mentioned, if it looks like what you need I'll fill it out if you make some data.
0
 

Author Comment

by:dynabytes
ID: 12014509

I looked at the link but its a little different to what i'm trying to do...

this line:
SET @SLOT_START = CONVERT(datetime, CONVERT(char(8), getdate(), 112) , 112 )

throughs this error:

Server: Msg 242, Level 16, State 3, Line 24
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

I've renamed the columns to my db columns and it all works fine except for that error above
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015021
ooops:
format 112 also has separators, thus requires 8+2 characters:
SET @SLOT_START = CONVERT(datetime, CONVERT(char(10), getdate(), 112) , 112 )
CHeers
0
 

Author Comment

by:dynabytes
ID: 12015183
Nope sorry I still get:

Server: Msg 242, Level 16, State 3, Line 24
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

This is the current SQL statement i'm using with all my correct column names:

DECLARE @SLOTS TABLE (  LOCATIONID INT, SLOT_START DATETIME , SLOT_END DATETIME, ROOM_STATE int )
DECLARE @SLOT_START DATETIME
DECLARE @SLOT_END DATETIME

-- get the current date (with no time)
SET @SLOT_START = CONVERT(datetime, CONVERT(char(10), getdate(), 112) , 112 )

--get the last and first slot start time (8am)
SET @SLOT_START = dateadd ( hour, 8, @SLOT_START )
SET @SLOT_END = dateadd ( hour, 8, @SLOT_START )

-- loop the day with increments of 30 minutes
WHILE @SLOT_START < @SLOT_END
BEGIN
  -- generate the rows for all the rooms for this time slot
   INSERT INTO @SLOTS
   SELECT LOCATIONID , @SLOT_START, dateadd ( minute, 30, @SLOT_START ), 1
   FROM LOCATIONS

   SET @SLOT_START= dateadd ( minute, 30, @SLOT_START )
END

-- now, update all the rows to set the FREE indicator to USED when the location is used in a meeting
UPDATE @SLOTS
SET ROOM_STATE = 0
FROM @SLOTS s
JOIN meetingitems m
-- the meeting item needs to be on the location/room of this slot
ON m.locationid = s.locationid
AND ( -- either the meeting items starts in the current slot
      m.itemstart >= s.SLOT_START AND m.itemstart < s.SLOT_END  
      OR -- or it ends in the current slot
      m.itemfinish > s.SLOT_START AND m.itemfinish <= S.SLOT_END
      OR -- or it englobes the current slot
      m.itemstart <= s.SLOT_START AND m.itemfinish >= S.SLOT_END
      )

SELECT * FROM @SLOTS
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015299
I see:
 [itemstart] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [itemfinish] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
What the ç*`?ç=*ç is that ?
if ItemStart and ItemFinish correspond to date/time, store the values in columns of that type...
What about MeetingDate, it is in datetime data type, is there maybe something I miss?

As workaround you might try this:

UPDATE @SLOTS
SET ROOM_STATE = 0
FROM @SLOTS s
JOIN meetingitems m
-- the meeting item needs to be on the location/room of this slot
ON m.locationid = s.locationid
AND ( -- either the meeting items starts in the current slot
      CONVERT( datetime, m.itemstart , <yourformat> ) >= s.SLOT_START AND m.itemstart , <yourformat> )< s.SLOT_END  
      OR -- or it ends in the current slot
      CONVERT( datetime, m.itemfinish , <yourformat> )> s.SLOT_START AND CONVERT( datetime, m.itemfinish , <yourformat> )<= S.SLOT_END
      OR -- or it englobes the current slot
      CONVERT( datetime, m.itemstart , <yourformat> )<= s.SLOT_START AND CONVERT( datetime, m.itemfinish , <yourformat> )>= S.SLOT_END
      )

replace <yourformat> by the numerical value indicated by the books online of SQL Server corresponding to the datetime format you use to store the datetime value in your fields...

CHeers
0
 

Author Comment

by:dynabytes
ID: 12015649
OK :) you make a very good point - i've converted the varchar field to a date/time field which makes the above suggestion of yours work. I developed it in a rush.

Only thing left now is to wrap it up in a stored procedure so I can pass date ranges and locationid's to it please.

Regards,
Mike
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 12015844
I would create the stored procedure to work for 1 day and 1/all rooms, call the proc if needed for several days.

The below procedure will allow to get the utilization for 1 date (by default for today), for either 1 room or all rooms.

CREATE PROC GET_ROOM_UTILIZATION
   @ON_DATE DATETIME = NULL,
   @FOR_ROOM INT = NULL
AS

DECLARE @SLOTS TABLE (  LOCATIONID INT, SLOT_START DATETIME , SLOT_END DATETIME, ROOM_STATE int )
DECLARE @SLOT_START DATETIME
DECLARE @SLOT_END DATETIME

-- get the current date (with no time)
IF @ON_DATE IS NULL
SET @SLOT_START = CONVERT(datetime, CONVERT(char(10), getdate(), 112) , 112 )
ELSE
SET @SLOT_START = CONVERT(datetime, CONVERT(char(10), @ON_DATE, 112) , 112 )

--get the last and first slot start time (8am)
SET @SLOT_START = dateadd ( hour, 8, @SLOT_START )
SET @SLOT_END = dateadd ( hour, 8, @SLOT_START )

-- loop the day with increments of 30 minutes
WHILE @SLOT_START < @SLOT_END
BEGIN
  -- generate the rows for all the rooms for this time slot
   INSERT INTO @SLOTS
   SELECT LOCATIONID , @SLOT_START, dateadd ( minute, 30, @SLOT_START ), 1
   FROM LOCATIONS
   WHERE LOCATION_ID = @FOR_ROOM
   OR @FOR_ROOM IS NULL

   SET @SLOT_START= dateadd ( minute, 30, @SLOT_START )
END

-- now, update all the rows to set the FREE indicator to USED when the location is used in a meeting
UPDATE @SLOTS
SET ROOM_STATE = 0
FROM @SLOTS s
JOIN meetingitems m
-- the meeting item needs to be on the location/room of this slot
ON m.locationid = s.locationid
AND ( -- either the meeting items starts in the current slot
      m.itemstart >= s.SLOT_START AND m.itemstart < s.SLOT_END  
      OR -- or it ends in the current slot
      m.itemfinish > s.SLOT_START AND m.itemfinish <= S.SLOT_END
      OR -- or it englobes the current slot
      m.itemstart <= s.SLOT_START AND m.itemfinish >= S.SLOT_END
      )

SELECT * FROM @SLOTS

GO
0
 

Author Comment

by:dynabytes
ID: 12015866
Sorry just a quick request.

I want to include location.locationname in the recordset and also want to order by / group by locationid

I'll try the stored procedure now
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015894
change the final SELECT in the stored procedure as needed:
>>SELECT * FROM @SLOTS

SELECT * FROM @SLOTS JOIN LOCATIONS
BY LOCATIONS.LOCATION_ID = @SLOTS.LOCATION_ID
ORDER BY @SLOTS.LOCATION_ID

CHeers
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015899
PS: as you will use the stored procedure from a program, you should add this line as first of the code body:
SET NOCOUNT ON
0
 

Author Comment

by:dynabytes
ID: 12015934
that syntax you gave me:
SELECT * FROM @SLOTS JOIN LOCATIONS
BY LOCATIONS.LOCATION_ID = @SLOTS.LOCATION_ID
ORDER BY @SLOTS.LOCATION_ID

Doesn't look right. I'm getting

Must declare the variable '@SLOTS'. As an error message
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12016072
The syntax is this:
SELECT * FROM @SLOTS JOIN LOCATIONS
ON  LOCATIONS.LOCATION_ID = @SLOTS.LOCATION_ID
ORDER BY @SLOTS.LOCATION_ID

Again, this will be part of the procedure, not outside of it

Cheers
0
 

Author Comment

by:dynabytes
ID: 12016101
OK this is an alter statement i've got:

Have I got the select statement (above) in the correct position as I am still getting the error: (sorry)

Server: Msg 137, Level 15, State 2, Procedure pr_RoomUtilisation, Line 52
Must declare the variable '@SLOTS'.



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROC pr_RoomUtilisation
   @ON_DATE DATETIME = NULL,
   @FOR_ROOM INT = NULL
AS

DECLARE @SLOTS TABLE (  LOCATIONID INT, SLOT_START DATETIME , SLOT_END DATETIME, ROOM_STATE int )
DECLARE @SLOT_START DATETIME
DECLARE @SLOT_END DATETIME

-- get the current date (with no time)
SET NOCOUNT ON

IF @ON_DATE IS NULL
SET @SLOT_START = CONVERT(datetime, CONVERT(char(10), getdate(), 112) , 112 )
ELSE
SET @SLOT_START = CONVERT(datetime, CONVERT(char(10), @ON_DATE, 112) , 112 )

--get the last and first slot start time (8am)
SET @SLOT_START = dateadd ( hour, 8, @SLOT_START )
SET @SLOT_END = dateadd ( hour, 8, @SLOT_START )

-- loop the day with increments of 30 minutes
WHILE @SLOT_START < @SLOT_END
BEGIN
  -- generate the rows for all the rooms for this time slot
   INSERT INTO @SLOTS
   SELECT LOCATIONID , @SLOT_START, dateadd ( minute, 30, @SLOT_START ), 1
   FROM LOCATIONS
   WHERE LOCATIONID = @FOR_ROOM
   OR @FOR_ROOM IS NULL

   SET @SLOT_START= dateadd ( minute, 30, @SLOT_START )
END

-- now, update all the rows to set the FREE indicator to USED when the location is used in a meeting
UPDATE @SLOTS
SET ROOM_STATE = 0
FROM @SLOTS s
JOIN meetingitems m
-- the meeting item needs to be on the location/room of this slot
ON m.locationid = s.locationid
AND ( -- either the meeting items starts in the current slot
      m.itemstart >= s.SLOT_START AND m.itemstart < s.SLOT_END  
      OR -- or it ends in the current slot
      m.itemfinish > s.SLOT_START AND m.itemfinish <= S.SLOT_END
      OR -- or it englobes the current slot
      m.itemstart <= s.SLOT_START AND m.itemfinish >= S.SLOT_END
      )

SELECT * FROM @SLOTS JOIN LOCATIONS
ON  LOCATIONS.LOCATIONID = @SLOTS.LOCATIONID
ORDER BY @SLOTS.LOCATIONID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 

Author Comment

by:dynabytes
ID: 12016133
Its ok - this fixed it:

SELECT * FROM @SLOTS S JOIN LOCATIONS
ON  LOCATIONS.LOCATIONID = S.LOCATIONID
order by s.locationid


0
 

Author Comment

by:dynabytes
ID: 12016843
OK - i've made a few tweaks to it and its working fine now, thanks so much for your help.

Unfortunately i've now used all my points - but I do have another question relating to another problem i'm having, would you mind if I emailed you the question? :) Kinda like a freebie I guess :)

Regards,
Mike
iwebdeveloper@msn.com
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now