SQL ISSUE... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL ISSUE...

Author

Topic

gvmk27
Starting Member

44 Posts

Posted - 2012-05-21 : 12:40:01

Hi

I was trying to develop appointment calendar, admin needs to define meeting frequencies
If a meeting is already booked from 9.00 to 13:00 and 14:00 to 15:00,
he can book a slot for 13:00 to 14:00. I was trying to check for the existing records using between, but below SQL show meeting already exists for that duration

declare @starttime time(0)
declare @endtime time(0)
set @starttime = '13:00:00'
set @endtime = '14:00:00'

SELECT CDSD.StartTime, CDSD.EndTime, CDSD.TimeSlotDuration
FROM CenterDowSetting CDS
INNER JOIN CenterDowSettingDetail CDSD
ON CDS.CenterDowSettingID = CDSD.CenterDowSettingID
WHERE CDS.DowID = @DowID -- DayId
AND (((StartTime < convert(time(0), @starttime)) AND (EndTime <= convert(time(0), @starttime)))
OR (EndTime >= convert(time(0), @endtime)))

Thanks for your help / advice in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 16:40:21

whats the datatype of STartTime and EndTime in CenterDowSettingDetail table?

Also current is looking only for slots that comes outside the range chosen.

i think the check should have been


IF NOT EXISTS(SELECT 1 FROM CenterDowSetting CDS
INNER JOIN CenterDowSettingDetail CDSD
ON CDS.CenterDowSettingID = CDSD.CenterDowSettingID
WHERE CDS.DowID = @DowID 
AND ((StartTime < @starttime AND EndTime >  @starttime)
OR (StartTime < @endtime AND EndTime >  @endtime ))
)
 SELECT 'No Conflicts'
ELSE 
 SELECT 'Conflicts'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2012-05-22 : 11:06:12

Thanks for your time Visakh..

Startime & endtime data type is Time(0)

But this gets failed in the below scenario

Meetings are already there for below interval

09:00:00 to 13:00:00
14:00:00 to 15:30:00
16:30:00 to 17:30:00

If I try to define for outer boundaries, I mean

08:00:00 to 18:00:00

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources