Add/Update Query with time check.

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

General SQL Server Forums

New to SQL Server Programming

Add/Update Query with time check.

Author

Topic

zafarthesultan
Starting Member

16 Posts

Posted - 2010-12-02 : 08:21:43

Hi everyone,

create table #timeslots
(
id int,
startHours int,
startMins int,
endHours int,
endMins int
)

insert into #timeslots
select 1,10,30,15,30 union all
select 2,9,30,10,45 union all
select 3,12,00,14,00 union all
select 4,4,20,8,50 union all
select 5,11,55,17,25

select * from #timeslots

drop table #timeslots

This table holds the start and end time for a task. The datatypes of the columns are as it is and can not be changed. Here is my problem.
for ID 1: The alloted time is 10:30 To 15:30
Now, if I have to update any record or add a record the time i will provide(hoursFrom, minsFrom, hoursTo, and minsTo) must not clash with any other record. That is, if I add a record or update a record with time starting or ending in between the start time or end time of any record the record must not saved. Can anyone please help me write query for this? Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 08:29:07

Good grief - how to make something difficult.
Easiest probably to convert to datetime to do the check.
so say you have the period to add in the variables
then

select *
from
(
select stm = dateadd(mi,startMins,dateadd(hh,startHours,0)) ,
,etm = dateadd(mi,endMins,dateadd(hh,endHours,0))
from #timeslots
) a
join
(
select stm = dateadd(mi,@startMins,dateadd(hh,@startHours,0)) ,
,etm = dateadd(mi,@endMins,dateadd(hh,@endHours,0))
) b
on a.stm < b.etm
and a.etm > b.stm

If that is not empty then you have an overlap
If you want to check after the addtion just change the second derived table to be the same as the first, add the id to it and add and a.id <> b.id to the join

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-02 : 08:29:13

But your provided sample data has already overlapping timeslots...
for example id 1: 10:30 to 15:30 and id 2: 09:30 to 10:45

No, you're never too old to Yak'n'Roll if you're too young to die.

zafarthesultan
Starting Member

16 Posts

Posted - 2010-12-04 : 00:02:04

Sorry for replying late. Thanks webfred. That worked for me. Thanks a lot. And @nigelrivett: friend that was just random data i typed while submitting the topic to the forum. Sorry anyways. Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-05 : 12:49:39

????
oh - I think you have the names swapped round

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources