Re: Finding the Shift for current hour

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" wrote:

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

Relevant Pages

  • Re: Monthly Totals Query
    ... The shifts have to come from somewhere, so you would need a table of shifts, and a query that outer-joins this to your existing table. ... If you don't already have one, create a table of the valid shift numbers. ... Turn this into into a crosstab query. ... In the Crosstab row, choose Row Heading. ...
    (microsoft.public.access.queries)
  • Re: Crosstab query for chart
    ... About filtering the records if the shift *control* (the control belongs to ... as long as the query is really a saved query (not an SQL ...
    (microsoft.public.access.queries)
  • Re: My Brain hurts. Query works in all but one case.
    ... INNER JOIN TblMoldRun AS T ... AND TblWhoWhen.Shift = ... Build a query that builds a FirstGoodPartDT column by adding the ... For the most part it works, except for second shift. ...
    (microsoft.public.access.queries)
  • Re: My Brain hurts. Query works in all but one case.
    ... AND TblWhoWhen.Shift = ... Build a query that builds a FirstGoodPartDT column by adding the ... For the most part it works, except for second shift. ... FROM TblWhoWhen INNER JOIN TblMoldRun AS T ON ...
    (microsoft.public.access.queries)
  • Re: make table query that totals from several tables
    ... Normalize your data with the following query: ... UNION ALL ... SELECT Month, shift, SUM ... > lost' The fields are date and calls lost. ...
    (microsoft.public.access.queries)