Hours per WorkWeek calculated from Start / Finish Dates?

[lottaviano]

My table lists pieces of equipment and the date/time they are taken
down for maintenance, and date/time maintenance is complete (put back
up to production). Sometimes these dates/times are within the same
WorkWeek, but sometimes a piece of equipment is down for multiple weeks
at a time. I need to calculate the % of time "up" per workweek.

For example, if a piece of equipment went down on Wednesday, June 7 and
was not back up until Wednesday, June 21 (2 weeks later), I need to
show that it was up roughly:
50% of WorkWeek 23
0% of WorkWeek 24
50% of WorkWeek 25

A few considerations:
1. What if the same piece of equipment goes down/up, then down again
in the same WorkWeek? The percent "up" needs to decrease accordingly.

2. What if a piece of equipment is not taken down at all. Can I
reflect 100% "up" without an entry in the table?

What else am I not thinking of? I have searched and searched these
groups and haven't found the exact solution I need.
Thanks,
Liana Ottaviano
Bend, Oregon

 

[Guest]

One other ting would be whether you are a 24-7 day operation. Equipment out
for a whole day would be up for 80% if you are 8-hour day and 5-day week
operations.

 

[lottaviano]

For the purposes of our calculations, we use a 168 hour workweek (24/7)
to calculate percentages.

 

[Michel Walsh]

Hi,

*if* you have a table with all the work day hours, like:

Works ' table name
Starting, Ending ' fields name
2001.01.01 8:00 2001.01.01 17:00:00
2001.01.02 8:00 2001.01.02 17:00:00
.... ' data sample


which would exclude holidays and weekends, so, *if* you have that kind of
table, then, you can find any overlap, intersection, of these periods with
any down time period:


DownTimes 'table name
Begining, Finishing' fields name
.... data similar to the previous data, but can span days, such as:
2001.01.10 6:45:28 2001.01.16 22:22:11



Indeed, a work interval does not overlap a downtime interval if, and only
if:

Ending < Beginning OR Starting > Finishing


So, there is some overlap in the negation:

Overlap if: Ending > Beginning AND Starting < Finishing


SELECT a.Starting, a.Ending, b.Beginning, b.Finishing
FROM works As a INNER JOIN downTimes as b
ON a.Ending > b.Beginning AND a.Starting < b.Finishing



To get the exact overlap in terms of working hour (since downtime outside
working hour does not bother us):

SELECT iif(a.Starting>b.Beginning, a.Starting, b.Beginning) As
trueStart,
iif( a.Ending > b.Finishing, a.Ending, b.Finishing) As trueEnd
FROM works As a INNER JOIN downTimes as b
ON a.Ending > b.Beginning AND a.Starting < b.Finishing


or, if you are interested by the downtime, per week:


SELECT
Nz(SUM( iif( Nz(a.Ending > b.Finishing, false), a.Ending,
b.Finishing)
- iif(Nz(a.Starting>b.Beginning, false), a.Starting,
b.Beginning) ), 0)
FROM works As a LEFT JOIN downTimes as b
ON a.Ending > b.Beginning AND a.Starting < b.Finishing
GROUP BY DatePart("ww", a.Starting)



should do. Note that if there is no downtime for a given day, the outer
join should give you a Null as sum, that Nz replaces with a 0.

-- it is untested-- I may have forget a parenthesis or something else.



Hoping it may help,
Vanderghast, Access MVP

 

[lottaviano]

Hi Michael,
I surely appreciate your help. You are the only one that has come
close to understanding my goal. Because my knowledge of Access is not
very in-depth, I have what may seem like a few simple questions:

1. Where do I put this code? I have a "Menu Dialog" that opens when
an operator opens the database. If I put a Command Button on there to
"Generate Downtime Report", would this code be part of the "On Click"
action for that button? What is the output of this code which should
appear on the report?

2. How do I generate a table with all the necessary workdays/times -
besides the obvious of typing each day individually? I tried to used
Excel to autofill a sequential date/time column, but it won't let me
concatenate the date/time together into one cell. Because we run 24
hours a day, would my entries would look like the following?

Starting, Ending
2006.07.01 12:00 AM 2006.07.01 11:59 PM
2006.07.02 12:00 AM 2006.07.02 11:59 PM

Thanks again for your advice,
Liana Ottaviano
Bend, Oregon

 

[Michel Walsh]

Hi,


The SQL statement could be the code (SQL view) of a saved query, and you use
the saved query as if it was an original table, to base built the report (or
sub-report).


To "concatenate" the day with the hour, have the days in a table, t1, have
the hours in another table, t2, then:

SELECT t1.day + t2.hour
FROM t1, t2


would do. BUT, in your case, use

SELECT t1.day As Starting, t1.day +1 As Ending
FROM myTableOfWorkingDays as t1


Indeed, days are stored as number of days since 30 December 1899, so, a
"date + 1" is just one day after the specified date. Since the SQL statement
uses < and >, not <= or >=, date+1 seems more appropriate than date+
23:59:59.


Hoping all that makes sense,
Vanderghast, Access MVP

 

[lottaviano]

Hi Michel,
I got my table of Dates created, no problem. Now I am working on the
SQL language. So far I have:

SELECT
Nz(SUM(iif(Nz(Ending > [up_date_time], false),
Ending,[up_date_time]) - iif(Nz(Starting> [down_date_time], false),
Starting, [down_date_time]) ), 0)
FROM Dates As a LEFT JOIN [Action_Log] as b
ON Ending > [down_date_time] AND Starting < [up_date_time]
GROUP BY DatePart("ww", Starting)

I keep getting an "Join Expression Not Supported" error with the text
after "ON" highlighted. I am wondering if I used the "FROM" line
correctly. Are "a" and "b" supposed to represent something else?

My two table names are "Dates" (the one with all the sequential dates
in it) and "Action Log" which contains two fields indicating when
equipment was brought out of service (down date time) and put back into
service (up date time). Will I have conflict if one is in the format
"06/30/06" and the other is "06/30/06 11:45:34 AM"?

Liana

 

[Michel Walsh]

Hi,

you have tried

.... ON a.Ending > b.down_date_time AND a.Starting < b.up_date_time GROUP BY
....

You should not have date_time problem, since internally, they are stored in
the same way; a date alone just get the time a midnight ( 00:00:00), a
little bit like 100 = 100.0000.

Just be sure that the same format sequence (mm/dd/yyy or dd/mm/yyyy or
.... ) is used to enter any new date.


Vanderghast, Access MVP

 

[lottaviano]

Hi Michel,

Good! Now I am no longer getting error messages with the SQL language.
However, when I go to run the query, I get the "Enter Parameter Value"
dialog boxes for "up_date_time" and "down_date_time". I don't see that
there should be any "relationship" between the two tables, but maybe
I'm wrong? I tried defining a relationship link between down_date_time
and Starting, but no help. What could be causing these dialog boxes to
appear?

BTW, I also get an error message when I go into Design View stating
"Microsoft Access can't represent the join expression in a.Starting <
b.up_date_time in Design View." Is that of any consequence?

Liana

 

[Michel Walsh]

Hi,


If you get asked for a parameter, maybe there is a typo somewhere. If the
field name has space in it, use [ ] around the field name, otherwise, be
sure it is exactly the field name as it appears in the design of the table.

The error about the design view is not relevant, it just that Access cannot
GRAPHICALLY represents the join.


Hoping it may help,
Vanderghast, Access MVP

 

[lottaviano]

Great! Now it is running with no errors. My result is a one-column
table with numbers of 12 decimal places. I assume that each row
represents a workweek. based on the "Group By" line. How do I get it
to also show me the Equipment Names, or other table fields for that
matter? Right now I think it is lumping together all pieces of
equipment rather than splitting them out separately.

I apologize for my SQL ignorance, but this is really teaching me. I
appreciate your responses very much!

 

[Michel Walsh]

Hi,


In a total query, a field involved in the SELECT must either also be in the
GROUP BY clause, either aggregated (SUM, MIN, MAX, FIRST, LAST, ... )



SELECT
EquipmentID, MIN(EquipmentName),
Nz(SUM(iif(Nz(Ending > [up_date_time], false),
Ending,[up_date_time]) - iif(Nz(Starting> [down_date_time], false),
Starting, [down_date_time]) ), 0)

FROM Dates As a LEFT JOIN [Action_Log] as b
ON a.Ending > b.[down_date_time] AND a.Starting < b.[up_date_time]

GROUP BY EquipmentID, DatePart("ww", Starting)



Here, I used MIN(EquipmentName) since, having GROUP BY EquipmentID, each
group would have just one EquipmentName repeated for all the records
"making" that group, so MIN is appropriate, as would do MAX, FIRST and LAST
too. I could have used:


SELECT
EquipmentID, EquipmentName,
Nz(SUM(iif(Nz(Ending > [up_date_time], false),
Ending,[up_date_time]) - iif(Nz(Starting> [down_date_time], false),
Starting, [down_date_time]) ), 0)

FROM Dates As a LEFT JOIN [Action_Log] as b
ON a.Ending > b.[down_date_time] AND a.Starting < b.[up_date_time]

GROUP BY EquipmentID, EquipmentName, DatePart("ww", Starting)





but that will take more time to run, since the GROUP BY clause has more
data, making the "sorting" or whatever algorithm is used to generate the
groups and test groups appartenance, slower. But that, logically, would do
fine too.



Hoping it may help,
Vanderghast, Access MVP

 

[lottaviano]

Ah, that's much better. My data doesn't seem to be calculating
correctly, however. For example, Equipment 1A was down for 4 weeks, 1
day, 2 hours, 5 minutes. This was spread over 5 work weeks (that part
is correct), with workweeks 20 and 24 being partially down, and 21-23
being completely down. I would expect that 21, 22 and 23 would max out
at 168 hours (number of hours in a week) or at least some constant
number.

Expr1001 EQUIP_NAME Expr1002
20 1A 137.447916666686
21 1A 151.131944444467
22 1A 117.722222222248
23 1A 53.1319444444671
24 1A 8.77083333334303

My SQL looks like this:
SELECT EQUIP_NAME, DatePart("ww", Starting),
Nz(SUM(iif(Nz(Ending >[UP DATE TIME], false), Ending,[UP DATE TIME]) -
iif(Nz(Starting>[DOWN DATE TIME], false), Starting,[DOWN DATE TIME]) ),
0)
FROM Dates AS a LEFT JOIN [ACTION LOG] AS b ON (a.Ending > b.[DOWN DATE
TIME]) AND (a.Starting < b.[UP DATE TIME])
GROUP BY EQUIP_NAME, DatePart("ww", Starting);

My "Dates" table does not have weekends removed, but should this make a
difference? Also, can I truncate the decimal places?

Liana

 

[lottaviano]

Thanks so much Michel,
I successfully demonstrated my database capabilities in a meeting
yesterday and was met with applause. Some of that applause should
rightfully be directed your way, so here you go:
<<applause>>

I ended up subtracting the result from 7 to get "uptime" per workweek
rather than "downtime" and then divided by 7 to get a percentage, and
rounded to 4 decimal places. Here was the final SQL clause:

SELECT EQUIP_NAME, DatePart("ww", Starting),
round(Nz(7-SUM(iif(Nz(Ending >[UP DATE TIME], false),[UP DATE
TIME],Ending) - iif(Nz(Starting>[DOWN DATE TIME], false),Starting,[DOWN
DATE TIME]) ), 0)/7,4)
FROM Dates AS a LEFT JOIN [ACTION LOG] AS b ON (a.Starting < b.[UP DATE
TIME]) AND (a.Ending > b.[DOWN DATE TIME])
GROUP BY EQUIP_NAME, DatePart("ww", Starting);

Thanks again. It works like a champ!
Liana

 

[Michel Walsh]

Hi,


I didn't do much, myself, actually. You foresee the product was the right
tool, you believe in your abilities to do it, you did the hard work, the
research, the trial and error, and so on, thus, it is definitively *your*
success.

Vanderghast, Access MVP