Calculating # of Shifts Between 2 Date/Times

[Guest]

Hello~

I am looking for some assistance in building a query or designing a module.
I would like to calcuate the number of shifts between a date/time stored in
a table and the current date/time. It would also be helpful if the formula
did not count the shift in which the event occured.


For example if the table had the following two records
ID
1 03/23/2006 02:00 PM
2 03/24/2006 06:55 AM
3 03/24/2006 07:57 AM

A query would return the following based on the difference between the
date/time storedin the table and the current time (if this was run on 3/24/06
@ 2pm).

ID Num_Shifts
1 2
2 1
3 0


Any help would be greatly appreciated!

Thanks
Mat

 

[Tom Ellison]

Dear Mat:

This would be a straight forward ranking problem:

SELECT ID,
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.DateTime > T.DateTime)
AS Num_Shifts
FROM YourTable T
WHERE DateTime > Something
AND DateTime < Now()
ORDER BY ID

Replace the correct name of your table and columns in the above.

The "Something" is the "date/time stored in a table".

Now() could be dangerous. If the date/time set for your computer is
incorrect, you could have unexplained incorrect behavior of the software.
On the other hand, if no future DateTime values are recorded, there would be
no need for this anyway. If you don't really need it, I strongly recommend
you remove it and eliminate possible problems.

Tom Ellison

 

[Guest]

Tom~

Thanks for responding and hopefully you will check in on this ... I guess I
was not specific in what I needed ... I need a datediff type expression that
can calculate the number of shifts between the time stored in the table and
the current time. I think my previous example was a little misleading ...
sorry ... If you need further information please let me know ...

Thanks again!

Mat

 

[Tom Ellison]

Dear Mat:

I do not know what you mean. Please try to make a more careful description
and post an example that is complete.

Tom Ellison

 

[Guest]

Tom~

Thanks for following up ... Hopefully this description will be a little more
adequate.

Currently I have a table of events and the time that they occured such as
below.

Table1
EventID DateTime
1 03/28/2006 10:50 PM
2 03/28/2006 11:45 AM
3 03/27/2006 12:05 PM

In a query I am trying to calculate the number of shifts between the the
datetime in table1 and the current date. I can get the number of hours or
minutes or days using the DateDiff calculation but I was hoping to find a way
to calculate the number of shifts between the table1 datetime and the current
time (or the time the query is run). The shifts times would be 7:01 am -
3pm, 3:01pm - 11pm, and 11:01pm - 7am. So for example the process would go
as such, if the query is run at 9:00am on 3/29 Table1.EventID #1 would fall
in the 3:01pm - 11pm shift range. Between that shift and the current shift
(7:01 am - 3pm the shift in which the report is run) there is 1 shift. In
the case of table1.eventid #2 the event falls in the 7:01 am - 3pm shift.
Between that shift and the current shift (7:01 am - 3pm the shift in which
the report is run) there are 2 shifts. In the case of table1.eventid #3 the
event falls on 7:01 am - 3pm shift and there are 5 shifts between that event
and the current shift.

What I am trying to get at is the number of shifts (based on the time above)
between when the query is run and the datetime of each table1.eventid. This
will help me determine if a particular action is needed.

I am relative new to access so I am sorry if it is difficult to understand
my request.
Thanks for your patience!

Mat

 

[Tom Ellison]

Dear Mat:

The next step would be to assign the shift number to each time of day. Once
you have that, take the difference in shift numbers plus the difference in
days times 3.

Use a table like this:

Shift Start Offset
1 07:01 AM 0
2 03:01 PM 0
3 11:01 PM -3

The offset allows you to take 3 shifts off any AM time because that is a
shift that started on the previous day.

I will now show you how I build up the formula, so you may learn. The
objective is to assign a ShiftNumber to any date and time.

In the Immediate pane, I write a formula, press Enter, and see the result:

? DateDiff("d", CDate("1/1/2000"), CDate("03/26/2006 01:19 AM"))
2276

There are 2276 days between January 1, 2000 and the shift of interest.
Multiply by 3 (number of shifts per day):

? DateDiff("d", CDate("1/1/2000"), CDate("03/26/2006 01:19 AM"))
6828

Add the shift number for 1:19 AM, which is shift 3:
6831

Now, if the shift is before noon, add in the Offset. You see, the shift 3
times that are past midnight are for the shift beginning the PREVIOUS day,
so the shift number is 3 shifts too high.

? DateDiff("d", CDate("1/1/2000"), CDate("03/26/2006 01:19 AM")) * 3 + 3 +
IIf(Hour(CDate("03/26/2006 01:10 AM")) < 13, -3, 0)
6828

This is the shift number for that date and time. For the dates and times
below, the shift numbers are as shown:

? DateDiff("d", CDate("1/1/2000"), CDate("03/27/2006 12:05 PM")) * 3 + 1 +
IIf(Hour(CDate("03/27/2006 12:06 PM")) < 13, 0, 0)
6832

? DateDiff("d", CDate("1/1/2000"), CDate("03/28/2006 10:50 PM")) * 3 + 2 +
IIf(Hour(CDate("03/28/2006 10:50 PM")) < 13, 0, 0)
6836

DateTime Shift Offset ShiftNo
03/26/2006 01:19 AM 3 -3 6828
03/27/2006 12:05 PM 2 0 6832
03/28/2006 11:45 AM 1 0 6835
03/28/2006 10:50 PM 2 0 6836

Now convert this to a formula with the source of the variable values from
the query:

The difference between ShiftNo values is the number of shifts between those
times. Not simple, but I don't think there's a simpler way.

DateDiff("d", CDate("1/1/2000"), CDate(T.DateTime)) * 3 + S.ShiftNo +
IIf(Hour(CDate(T.DateTime)) < 13, S.Offset, 0)

In the above, the table S is the new Shift table alias. I have yet to show
you how to include this in the query. I'll make another post of that soon.

Tom Ellison

 

[Tom Ellison]

Dear Mat:

Here's some tested SQL for the rest of the problem:

SELECT *,
DateDiff("d", CDate("1/1/2000"), CDate(T.DateTime)) * 3 + S.ShiftNo +
IIf(Hour(CDate(T.DateTime)) < 13, S.Offset, 0)
AS ShiftNum
FROM YourTable T, Shift S
WHERE S.ShiftStart =
Nz((SELECT MAX(S1.ShiftStart)
FROM Shift S1
WHERE S1.ShiftStart < TimeValue(T.DateTime)),
(SELECT MAX(ShiftStart) FROM Shift))
ORDER BY T.DateTime

The results I have are:

ShiftNum DateTime ShiftStart ShiftNo Offset
6820 3/23/2006 2:00:00 PM 7:01:00 AM 1 0
6822 3/24/2006 6:55:00 AM 11:01:00 PM 3 -3
6823 3/24/2006 7:57:00 AM 7:01:00 AM 1 0
6828 3/25/2006 11:08:00 PM 11:01:00 PM 3 -3
6828 3/26/2006 1:11:00 AM 11:01:00 PM 3 -3
6832 3/27/2006 12:05:00 PM 7:01:00 AM 1 0
6835 3/28/2006 11:45:00 AM 7:01:00 AM 1 0
6836 3/28/2006 10:50:00 PM 3:01:00 PM 2 0


Does this finish up what you were wanting?

Tom Ellison

 

[Guest]

Tom~

Your are a Genuis and that is an undeniable fact!

It took a little tweaking but I was able to follow exactly each step of your
process and tailor it to my database. The result is that it works flawlessly!

Again Many Thanks (and even that is not enough!)
Mat