• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

Room free/busy time - simple query

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
dynabytes
Asked:
dynabytes
  • 10
  • 9
  • 2
1 Solution
 
Ken SelviaRetiredCommented:
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
 
dynabytesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dynabytesAuthor Commented:
Some code would be tops - I ain't a SQL guru - more of a web developer :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
PS: the above is not tested for syntax...
0
 
Ken SelviaRetiredCommented:
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
 
dynabytesAuthor Commented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ooops:
format 112 also has separators, thus requires 8+2 characters:
SET @SLOT_START = CONVERT(datetime, CONVERT(char(10), getdate(), 112) , 112 )
CHeers
0
 
dynabytesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dynabytesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dynabytesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dynabytesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dynabytesAuthor Commented:
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
 
dynabytesAuthor Commented:
Its ok - this fixed it:

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


0
 
dynabytesAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now