date time question

Question

date time question

rs-337036

Ten Centuries

Points: 1105
More actions
November 15, 2007 at 12:03 pm

#62194

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

EvilPostIT SSCrazy Points: 2578 More actions · Answer 1

I would say put a trigger on the table to check if there are any appointments within 15 mins of the proposed new appointment.

Nuke the site from orbit, its the only way to be sure... :w00t:

rs-337036 Ten Centuries Points: 1105 More actions · Answer 2

do u have an example code for that. I am k with sql no that good.

Thanks

goodguy SSCertifiable Points: 5456 More actions · Answer 3

[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.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

rs-337036 Ten Centuries Points: 1105 More actions · Answer 4

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.

R2ro Default port Points: 1439 More actions · Answer 5

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

--