pradeep tn

I have a table which stores the shift timing like this:

ShiftId StartTime EndTime WeekDay Shift
1 6:00:00 AM 6:00:00 PM 7 Shift A
2 6:00:00 PM 6:00:00 AM 7 Shift B
4 6:00:00 AM 6:00:00 PM 112 Shift C
8 6:00:00 AM 6:00:00 PM 112 Shift D
16 8:00:00 AM 5:00:00 PM 62 Day
32 8:00:00 AM 5:00:00 PM 31 Shift E
64 12:00:00 AM 12:00:00 PM 112 Midnight

Each user has a shift assigned. In my application, whenever there is a
certain event, I need to notify (email) the users about the event. For this I
need to find out to which shift the current time belongs (so that I notify
only users belongin to the proper shift). For ex, if an event has occured at
5:30 AM I need to find out all the shifts.

I am finding it difficult to write the SQL for this since the timing are
overlapping. Please help me with the SQL.

hugo kornelis

Hi Pradeep TN,

Try if this works:

DECLARE @EventMoment datetime
SET @EventMoment = '00:05:30' -- Use 24h clock and hh:mm:ss notation

SELECT ShiftId, Shift
FROM Shifts
WHERE @EventMoment <= EndTime
-- Case 1: "normal" shifts
AND (( @EventMoment >= StartTime
AND EndTime >= StartTime)
-- Case 2: shifts with EndTime before StartTime (these include midnight)
OR ( @EventMoment <= StartTime
AND EndTime <= StartTime))

(Untested - see http://www.aspfaq.com/5006 if you prefer a tested solution)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

pradeep tn

Hi Hugo Kornelis,

Thank you very much for your reply. The query is working fine. However, I
had to make one change in my data since the query failed on one condition.
Since I was only interested in "time" part, I had not looked properly into
the "date" part. When I insert rows for the given shift information, the
"date" part defaults to '1899-12-30'. I am not sure why this is so, since the
base date is supposed to be '1900-01-01'.

If you look at Shift B, it starts from 6 PM - 6AM. Now if '1899-12-30' is
supposed to be by "base date" for this table, 6 AM represents 6 AM of
'1899-12-31'. So made that change in the table and your query is working
fine. Before this change, if my @EventMoment was '18:10:00', there were no
rows returned.

Once again thanks for you help.

--Pradeep

hugo kornelis

Hi Pradeep,

Are you using Enterprise Manager to isnert the data? For some mysterious
reason, the deveopers who wrote EM seem not to have tallked to the
developers who wrote the core data handling of SQL Server - instead of
asking them for the default date, they chose their own. 1899-12-30, as
you might have guessed.


Yeah, my query assumed that the times were stored with the correct
default date. Glad you were able to sort this last problem out by
yourself!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)