November 15, 2007 at 12:03 pm
I have one table in which i store the starting time and ending time of an appointment. The table is called schedule_info.
I want to enter appointment in the table at an 15 min interval. for example there is one appointment
start_time= 2007-11-14 10:00:00.000
end_time = 2007-11-14 11:00:00.000
so it is from 10 am to 11 am.
If some one tries to make an appointment from11 am(end _time) i want to throw an error that u can make appointmnet from 11 :15 not 11.
So there needs to be gap of 15 min on every appointment.
here is the table structure
CREATE TABLE [Schedule_Info] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[_Date] [datetime] NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PersonResponsible] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Starting_Time] [datetime] NULL ,
[Ending_Time] [datetime] NULL ,
[AMPM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Approve] [bit] NULL ,
[userid] [int] NULL ,
[serverid] [int] NULL ,
[Customer_status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[number_of_seats] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Booked] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_date] [datetime] NULL
) ON [PRIMARY]
GO
November 15, 2007 at 3:33 pm
I would say put a trigger on the table to check if there are any appointments within 15 mins of the proposed new appointment.
November 15, 2007 at 3:42 pm
do u have an example code for that. I am k with sql no that good.
Thanks
November 16, 2007 at 12:30 am
[Code]CREATE TRIGGER CHECKENDTIMES ON [dbo].[Schedule_Info]
FOR INSERT, UPDATE
AS
DECLARE @START DATETIME
DECLARE @END DATETIME
SET @START = SELECT STARTING_TIME FROM INSERTED
SET @END = SELECT ENDING_TIME FROM INSERTED
SELECT COUNT(TITLE) AS CLASH
FROM SCHEDULE_INFO
WHERE DATEDIFF(m, ENDING_TIME, @START) < 15
IF CLASH > 0 THEN
ROLLBACK
END[/Code]
I am not a pro in SQL but I am learning, and I have posted this answer as much for myself as for the OP, so I beseech those who would like to bite my head off to make a grand start to their weekends to hold their horses please. This code has not been tested, I just wrote it on the fly.
November 16, 2007 at 12:37 pm
This is what i have right now but it is not working fully
example data is Date is 11/19/2007
id=1
starting_time= 11/19/2007 3:00:00 AM
ending_time = 11/19/2007 4:00:00 AM
DECLARE @STARTing_time DATETIME
DECLARE @ENDing_time DATETIME
SET @STARTing_time = '11/19/2007 4:00:00 AM'
SET @ENDing_time = '11/19/2007 5:00:00 AM'
select * from Schedule_Info
where Ending_Time > dateadd(mi, -15, @Starting_Time)and Starting_Time < dateadd(mi, -15, @Ending_Time)
and serverid='1' AND _date='11/19/2007' order by starting_time
It works when i select from the ending time
example if the appointment is from 3 am to 4 am and if i select from 4am to 5am then it works.
if i select from 2 am to 3 am then it doesn't work.
November 16, 2007 at 2:02 pm
Here's a little more elaborate but also potentially more flexible solution. It is still a prototype but seems to deliver on most of the key questions that might arise when checking a schedule.
I would do more with it but it's FRIDAY and I am outta here!
SP parameters - Result set
All NULL - all available times
apptStart only - all available time from apptStart
apptEnd only - all available time until apptEnd
minGap only - all available time greater than minGap
apptStart & apptEnd - empty set if time between available, rows of conflicts otherwise
all parms - all available time between start & end with minGap
Currently @prmApptDuration is not fully implemented as the start + duration availability list and end - duration availability list are not handled.
Test this before use but I think the gist of it is fairly clear.
USE tempdb
go
CREATE TABLE dbo.Schedule_Info
(
Starting_Time datetime NOT NULL,
Ending_Time datetime NOT NULL,
CONSTRAINT PK_schedule
PRIMARY KEY CLUSTERED (Starting_Time,Ending_Time)
)
go
CREATE VIEW vw_ScheduleGaps
(
BlockBeg,
BlockEnd,
AvailableTime
)
AS
SELECT
sb1.Ending_Time AS GapStart,
MIN(sb2.Starting_Time) AS GapEnd,
DATEDIFF(mi, sb1.Ending_Time, MIN(sb2.Starting_Time)) AS TimeAvailable
FROM Schedule_Info sb1
LEFT OUTER JOIN Schedule_Info sb2 ON
sb1.Ending_Time < sb2.Starting_Time
GROUP BY sb1.Ending_Time
go
CREATE PROCEDURE dbo.getAvailableAppointments
(
@prmApptStart datetime = NULL,
@prmApptEnd datetime NULL,
@prmApptDuration datetime = NULL,
@prmMinGap int = 0 -- shortest time between end of last & start of next appt
)
AS
BEGIN
SET @prmMinGap = COALESCE(@prmMinGap, 0)
-- no point scheduling in the past
IF @prmApptStart < CURRENT_TIMESTAMP
SET @prmApptStart = CURRENT_TIMESTAMP
ELSE
SET @prmApptStart = COALESCE(@prmApptStart, CURRENT_TIMESTAMP) -- NULL isn't a good time for me...
-- put an end to it
IF @prmApptEnd < CURRENT_TIMESTAMP
SET @prmApptEnd = CAST('12/31/9999 23:59:59.997' AS datetime)
ELSE
SET @prmApptEnd = COALESCE(@prmApptEnd, CAST('12/31/9999 23:59:59.997' AS datetime))
-- 'fix' it when appt ends before it starts
IF @prmApptEnd < @prmApptStart
SET @prmApptEnd = @prmApptStart
SELECT dt.*
FROM
(
SELECT
BlockBeg,
BlockEnd,
AvailableTime
FROM vw_ScheduleGaps
WHERE
BlockBeg >= @prmApptStart
UNION
SELECT
BlockBeg,
BlockEnd,
AvailableTime
FROM vw_ScheduleGaps
WHERE
BlockEnd <= @prmApptEnd
) dt
WHERE
dt.BlockBeg >= @prmApptStart AND
dt.BlockEnd <= @prmApptEnd AND
dt.AvailableTime >= @prmMinGap + COALESCE(@prmApptDuration, @prmMinGap)
RETURN
END
go
--
-- TABLE INSERT STATEMENTS
--
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/16/2007 08:00:00 AM', '11/16/2007 08:45:00 AM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/16/2007 09:00:00 AM', '11/16/2007 09:45:00 AM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/16/2007 10:00:00 AM', '11/16/2007 10:30:00 AM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/16/2007 11:15:00 AM', '11/16/2007 11:45:00 AM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/16/2007 12:00:00 PM', '11/16/2007 12:45:00 PM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/16/2007 01:00:00 PM', '11/16/2007 02:45:00 PM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/16/2007 04:00:00 PM', '11/16/2007 04:45:00 PM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/17/2007 09:00:00 AM', '11/17/2007 09:45:00 AM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/17/2007 11:00:00 AM', '11/17/2007 11:17:00 AM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/17/2007 11:45:00 AM', '11/17/2007 12:30:00 PM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/17/2007 01:08:00 PM', '11/17/2007 01:45:00 PM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/17/2007 02:03:00 PM', '11/17/2007 02:45:00 PM' )
go
INSERT INTO dbo.Schedule_Info ( Starting_Time, Ending_Time )
VALUES ( '11/17/2007 04:00:00 PM', '11/17/2007 04:45:00 PM' )
go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply