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.
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
If you look at Shift B, it starts from 6 PM - 6AM. Now if '1899-12-30' is
'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
"Hugo Kornelis" wrote:
> On Sat, 5 Nov 2005 07:43:02 -0800, "Pradeep TN" <Pradeep
> TN@discussions.microsoft.com> 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)
