Re: Finding the Shift for current hour

On Sat, 5 Nov 2005 07:43:02 -0800, "Pradeep TN" <Pradeep
TN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

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 www.aspfaq.com/5006 if you prefer a tested solution)

Best, Hugo
--

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