Re: Calculate Total Monthly Time not Covered
John Vinson
<Micha...@discussions.microsoft.com> wrote:
> I am attempting to pull the fields and calculate the total
>time outside of a range of Start Date/Start Time through End Dates/End Times
>to show the total time that I do not have around-the-clock employee coverage.
It's always harder to evaluate a negative (time NOT covered) than a
positive!
One idea might be to create a table, Num, with values from 0 through
60*24*31 = 44640; you could then create a Query like (air code,
complex and untested!!):
SELECT Count(*) FROM
(SELECT DISTINCT TimeCovered
FROM (SELECT DateAdd("n", N, DateSerial([Enter year:], [Enter month:],
1) AS TimeCovered WHERE N < 1440*Day(DateSerial([Enter year:], [Enter
month:] + 1, 0)) AS AllTime
INNER JOIN EmployeeTime_tbl
ON TimeNotCovered >= EmployeeTime_tbl.StartTime
AND TimeNotCovered <= EmployeeTime_tbl.EndTime
WHERE EmployeeTime_tbl.EndTime < DateSerial([Enter year:], [Enter
month:] + 1, 1)
AND EmployeeTime_tbl.StartTime >= DateSerial([Enter year:], [Enter
month:], 1));
The idea is to create a virtual table (the subquery after the second
FROM) with one record every minute during the user-selected month;
JOIN this to the employee table using a non-equi join to include those
minutes during the month which fall within a period which either ended
after the month began, or started before it ended. The query would
then just count those minutes.
John W. Vinson[MVP]
Michaniker
clause. You said there might be an easier way to determine time covered?
--
Michaniker
Michaniker
calculated total time during the month and am really seeking the easy
solution -- time covered. Will it help if I rephrase the question.
How To Calculate the Total on-the-clock time, by Month, in MSAccess 2000
StartDate EndDate
1/1/2005 00:01:00 AM 1/1/2005 02:00:00 PM
1/1/2005 00:01:00 AM 1/1/2005 09:01:00 AM
1/1/2005 23:00:00 AM 1/2/2005 12:35:00 PM
1/28/2005 06:30:00 AM 1/31/2005 07:00:00 PM
1/31/2005 06:30:00 AM 2/01/2005 21:31:00 PM
2/3/2005 00:01:00 AM 2/04/2005 23:59:00 PM
Above is representative of a table (TimeTable) capturing my employee's time
on-the-clock. Each row represents the time a different employee clocked-in
(StartDate) and clocked-out (EndDate).
I am attempting to pull the fields and calculate the total on-the-clock
time, by month, in an attempt to show the total time that I do not have
around-the-clock employee coverage. If the business is to run 24/7, this an
important calculation so I can tell my boss how many more personnel I need to
hire to get complete coverage. The difficulty I see in the rows are:
1. Some have overlapping times
2. Some have some equivalent clock-in time
3. Some could have equivalent clock-out times
4. Some EndDates exceed the end of month cut-off.
5. Some StartDates will preceed the start of a month.
For the above figures, I have an answer which was manually done.
-Jan 2005 has 31 days or 744 hours. Using the above table shows employee
on-the-clock time as 117.05 hours therefore I do not have coverage for 626.95
hours.
-Feb 2005 has 28 days or 672 hours. Using the above table shows employee
on-the-clock time as 69.47 hours.
What would the formula look like to get the total on-the-clock time, by month,
in a query (or queries) using MSAccess 2000? I imagine this could be pulled
off via query and pivot table.
--
Michaniker
John Vinson
<Micha...@discussions.microsoft.com> wrote:
>Wow, John. That was complicated. The From Clause broke. I do have a method of
>calculated total time during the month and am really seeking the easy
>solution -- time covered. Will it help if I rephrase the question.
>
I'm not surprised - it got more and more complicated as I was thinking
through the issues, and as I said it was "air code" without an actual
table.
It's a challenge though - I'll try setting up a test database and see
if I can work it through. I'm fighting a wretched cold though so
please be patient.
Anyone else want to tackle this? It's a fun little SQL logic problem
(for certain values of "fun").
John W. Vinson[MVP]
John Viescas
Let's start by pairing up each record with any later overlapping records:
SELECT TT1.StartDate + TT1.StartTime As Start1,
TT1.EndDate + TT1.EndTime As End1,
TT2.StartDate + TT2.StartTime As Start2,
TT2.EndDate + TT2.EndTime As End2
FROM MyTimeTable As TT1, MyTimeTable As TT2
WHERE TT2.Record > TT1.Record
AND Start2 <= End1 AND End2 >= Start1
AND NOT EXISTS
(SELECT * FROM MyTimeTable As TT3
WHERE TT3.Record < TT1.Record
AND (TT3.StartDate + TT3.StartTime) <= (TT1.EndDate + TT1.EndTime)
AND (TT3.EndDate + TT3.EndTime) >= (TT1.StartDate + TT1.StartTime)
The NOT EXISTS makes sure any record already paired with an earlier one
doesn't show up again.
You should be able to throw that result into a Totals query to find the Max
End for any given Start. The output of the Totals query should give you
rows with non-overlapping min start and max end - from which you can
calculate the total time covered to determine the total not covered.
John Viescas, author
"Building Microsoft Office Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
Access MVP since 1993
http://www.viescas.com/
"Michaniker" <Micha...@discussions.microsoft.com> wrote in message
news:81F63360-FBE2-4831...@microsoft.com...
> Record Start Date Start Time End Date
> End
> Time Month
>
> 1 1/1/2005 00:01 1/1/2005
> 02:00 200501
>
> 2 1/1/2005 01:30 1/1/2005
> 09:01 200501
>
> 3 1/1/2005 23:00 1/2/2005
> 12:35 200501
>
> 4 1/30/2005 06:30 1/31/2005
> 21:31 200501
>
>
> Above is representative of a table (EmployeeTime_tbl) capturing employee's
> time on the clock. I am attempting to pull the fields and calculate the
> total
> time outside of a range of Start Date/Start Time through End Dates/End
> Times
> to show the total time that I do not have around-the-clock employee
> coverage.
> The fact that I have overlapping times in the records decreases the amount
> of
> time not covered during the month. The estimate of total time where no
> employees are on the clock will be calculated on a monthly basis as seen
> by
> the six digit numerical field labeled Month (YYYYMM). What would the
> formula
> look like to pull this off using a query MSAccess 2000?
>
> --
> Michaniker
>
![HSalim[MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjXmS9YabrgVkvvFB4biQMEKDieZDMKIaQ3Pnxv7ccB0K3rCTg=s40-c)
HSalim[MVP]
Interesting problem.
Talking conceptually, your answer is fairly simple.
EndTime minus StartTime would give you Coverage, and Sum(Coverage), group by
Period (200501 in your example )would give you total hours covered. (you
could even use adjusted start and end times to ensure that it all falls in
one month)
Deduct sum coverage from number of hours in a month and you would have your
answer.
This summary answer does not tell you which slots are wide open and which
ones are overcrowded, so
it seems to me that you would have the wrong answer. Overlapping times
should NOT deduct from total coverage because your business cannot/shold not
run unattended for any period of time.(right?)
That is, if three employees were scheduled from 9 AM to 5 PM that does not
mean you have 24 hour coverage, right?
The question you might ask is:
What is my coverage by hour (or QtrHour or minute)? The answer should have a
list of time-slots and number of employees against it. (or scheduled
man-hours; if you have 1 employee for that slot, then time slot span would
equal man-hours)
To make this even more useful, create a time slot table with a min/max
coverage.
You can then generate a report for time slots are under or over scheduled,
and plan your scheduling accordingly.
So, define your needs and I'm sure we can give you the solution
HS
"Michaniker" <Micha...@discussions.microsoft.com> wrote in message
news:D7C479CB-D526-44BD...@microsoft.com...
James A. Fortune
> Record Start Date Start Time End Date End
> Time Month
>
> 1 1/1/2005 00:01 1/1/2005
> 02:00 200501
>
> 2 1/1/2005 01:30 1/1/2005
> 09:01 200501
>
> 3 1/1/2005 23:00 1/2/2005
> 12:35 200501
>
> 4 1/30/2005 06:30 1/31/2005
> 21:31 200501
>
>
> Above is representative of a table (EmployeeTime_tbl) capturing employee’s
> time not covered during the month. The estimate of total time where no
> employees are on the clock will be calculated on a monthly basis as seen by
>
This looks like a very interesting problem. John Viescas' solution was
well thought out and presented well. I was a little concerned about the
possible speed hit when using NOT EXISTS.
I'll give it try for fun and solution diversity. I'm starting to come
up with a plan of attack. I don't know in advance how good the solution
will be, if any, based on this plan.
MyTimeTable
RecordID Probably Autonumber
Start Date Date/Time m/d/yyyy
Start Time Short Time
End Date Date/Time m/d/yyyy
End Time Short Time
theMonth Text
RecordID Start Date Start Time End Date End Time theMonth
1 1/1/2005 0:01 1/1/2005 2:00 200501
2 1/1/2005 1:30 1/1/2005 9:01 200501
3 1/1/2005 23:00 1/2/2005 12:35 200501
4 1/30/2005 6:30 1/31/2005 21:31 200501
I'd like the SQL to calculate the coverage for all the months at once at
first. My idea is to get a unique list of the boundary points (all
possible Start or End Nodes), then check for a matching Start Node to
see whether the time atom is covered (Start Node match) or not.
Ideally, later on I'd like qryTimeNodes to include if a Start Node
contributed to its being there along with the time node value itself.
Only the left Time Node is needed to check against Start Nodes so of the
two end Time Nodes (start and end of the month) I only need the start of
the month Time Node for now. I had to get a little tricky in order to
get that Node in the query:
qryTimeNodes:
SELECT RecordID, theMonth, [Start Date] + [Start Time] AS TimeNode FROM
MyTimeTable UNION SELECT RecordID, theMonth, [End Date] + [End Time]
FROM MyTimeTable AS TimeNode UNION SELECT 1 AS RecordID, theMonth,
DateSerial(Left([theMonth], 4), Right([theMonth], 2), 1) FROM
MyTimeTable AS TimeNode ORDER BY theMonth, TimeNode;
!qryTimeNodes:
RecordID theMonth TimeNode
1 200501 1/1/05
1 200501 1/1/05 12:01:00 AM
2 200501 1/1/05 1:30:00 AM
1 200501 1/1/05 2:00:00 AM
2 200501 1/1/05 9:01:00 AM
3 200501 1/1/05 11:00:00 PM
3 200501 1/2/05 12:35:00 PM
4 200501 1/30/05 6:30:00 AM
4 200501 1/31/05 9:31:00 PM
I'll need to add another month of test data. Anyway, this is a start
toward a possible solution. This looks like it should be an interesting
learning experience for me. I'll spend a little time on it this weekend
to see if I can come up with something elegant. I don't think that
there are any easy solutions for this one.
James A. Fortune
MPAP...@FortuneJames.com
CDMAP...@fortunejames.com
Adjustment to the plan:
I would like pairs of Duration, Covered so that I can sum the Durations
where Covered = -1 to determine how much coverage to subtract from the
total duration of the month. This means that I don't need to push the
TimeNode for the start of the month into the list of TimeNodes since
the earliest TimeNode that contributes to Coverage will already be in
the list. But it does mean that I will need the TimeNode for the end
of the month when a following TimeNode doesn't exist (using the NZ
function) so that the final duration can be calculated. One way to
determine if the Duration is covered is by analogy with a technique for
checking for balancing parentheses.
I added the following record to MyTimeTable:
RecordID Start Date Start Time End Date End Time theMonth
5 2/1/2005 3:33 2/2/2005 0:30 200502
qryTimeNodes:
SELECT theMonth, [Start Date] + [Start Time] AS TimeNode, -1 As
StartTimeNode FROM MyTimeTable UNION SELECT theMonth, [End Date] + [End
Time] AS TimeNode, 1 As StartTimeNode FROM MyTimeTable ORDER BY
theMonth, TimeNode;
!qryTimeNodes:
theMonth TimeNode StartTimeNode
200501 1/1/2005 12:01:00 AM -1
200501 1/1/2005 1:30:00 AM -1
200501 1/1/2005 2:00:00 AM 1
200501 1/1/2005 9:01:00 AM 1
200501 1/1/2005 11:00:00 PM -1
200501 1/2/2005 12:35:00 PM 1
200501 1/30/2005 6:30:00 AM -1
200501 1/31/2005 9:31:00 PM 1
200502 2/1/2005 3:33:00 AM -1
200502 2/2/2005 12:30:00 AM 1
So the idea for determining coverage is that when the sum of the
StartTimeNode values up to and including the current TimeNode is
negative then at least one interval is unclosed and the time until the
next TimeNode is covered. Now I just have to check the logic and
finish putting in the details.
James A. Fortune
CDMAP...@FortuneJames.com
HSalim[MVP]
a fun challenge.
I've created a sample database with about 850 records that would represent
the data below. This will help test various scenarios. I've consolidated
the date and time portions of Start and End dates into one field each,
partly to simplify the query , partly because in my reading of Michanker's
post of 3/15 9.53 AM it seems that the data may indeed be stored that way.
I also created a small procedure that would analyze the data as I had
mentioned in my earlier post, but I have not spent much time on trying to do
this in just one or more queries.
I suspect that it may not be possible to do this using just access queries,
but that is what got me interested in this thread.
Working with one set of sample records might be helpful to exchange ideas
and I'm happy to share the one I created.
I do not have a public site to exchange files, so if you are interested,
send me an email and I'll send it out
Regards
HS
<CDMAP...@FortuneJames.com> wrote in message
news:1142866273....@i40g2000cwc.googlegroups.com...
James A. Fortune
> This is an interesting query and a fellow MVP brought it to my attention as
> a fun challenge.
>
> I've created a sample database with about 850 records that would represent
> the data below. This will help test various scenarios. I've consolidated
> the date and time portions of Start and End dates into one field each,
> partly to simplify the query , partly because in my reading of Michanker's
> post of 3/15 9.53 AM it seems that the data may indeed be stored that way.
>
> I also created a small procedure that would analyze the data as I had
> mentioned in my earlier post, but I have not spent much time on trying to do
> this in just one or more queries.
> I suspect that it may not be possible to do this using just access queries,
> but that is what got me interested in this thread.
>
> Working with one set of sample records might be helpful to exchange ideas
> and I'm happy to share the one I created.
> I do not have a public site to exchange files, so if you are interested,
> send me an email and I'll send it out
>
> Regards
> HS
I'd love to have a nice data file to test this out, especially as a
gauge of its efficiency. Please send a copy to: MPAPoster at
FortuneJames dot com
I'll also try sending you an email.
Here are a few thoughts I had since this morning:
I signed with CDMAP...@FortuneJames.com instead of
MPAP...@FortuneJames.com
This was because I was linking through a university account and had to
use Google to post. Normally, I use Google to post to
Comp.Databases.MS-Access and Mozilla to post to microsoft.public.access
and got confused by the context.
Because of the change to qryTimeNodes it may be possible to get a pair
of records with the same TimeNode value but different StartTimeNode
values. If that occurs it doesn't matter since the Duration will be 0
and no contribution will be made to Coverage either way.
Using [Start Date] + [Start Time], although relatively safe, is still
somewhat implemention dependent. Perhaps I should use built-in
functions when adding a time to a date. Anyway, I'm glad you've
consolidated them.
Thanks,
James A. Fortune
MPAP...@FortuneJames.com
Some interesting homemade videos (none by me):
http://www.youtube.com/browse?s=mf
HSalim[MVP]
As requested, I have emailed you a rar archive containing a compressed file.
I changed the file extension to .md_ , so remember to change it back to .mdb
after extracting the file.
Regards
HS
"James A. Fortune" <jimfo...@compumarc.com> wrote in message
news:Oa2g5XFT...@tk2msftngp13.phx.gbl...
James A. Fortune
The test file (tblSchedule) contained 850 records with an ID that I
named RecordID and start and end times called Start and End covering a
period of just over three months starting on 12/29/04 and ending on
3/1/05. The first thing I noticed when importing the file was that an
interval usually covered the transition from one month to the next. Any
time the month boundary is spanned, a TimeNode containing the beginning
of the month needs to be included. I also made RecordID a primary key
and indexed the other fields. Without those auxiliary month start
values the translation query is something like:
SELECT RecordID, Start, End, Year([Start]) & Format(Month([Start]),'00')
AS theMonth INTO MyTimeTable FROM tblSchedule;
I'm guessing that the Coverage for January and February is the test. I
tried my earliest query:
SELECT RecordID, theMonth, Start AS TimeNode FROM MyTimeTable UNION
SELECT RecordID, theMonth, End FROM MyTimeTable AS TimeNode UNION SELECT
1 AS RecordID, theMonth, DateSerial(Left([theMonth], 4),
Right([theMonth], 2), 1) FROM MyTimeTable AS TimeNode ORDER BY theMonth,
TimeNode;
1703 records came up.
Next I tried the query I posted:
qryTimeNodesPosted:
SELECT theMonth, Start AS TimeNode, -1 As StartTimeNode FROM MyTimeTable
UNION SELECT theMonth, End AS TimeNode, 1 As StartTimeNode FROM
MyTimeTable ORDER BY theMonth, TimeNode;
Without the auxiliary values for the month start it produced 1518
values. Any end times past the end of the month can use the end of the
month instead when calculating durations. IIf might solve that but it's
another potential slow down.
Note that the running sum of StartTimeNode values should never be
greater than 0.
To get that to happen correctly (qryTimeNodesPosted lost duplicate
TimeNodes) I adjusted qryTimeNodesPosted to include the RecordID so that
the DISTINCT effect of the Union Query would not toss them away:
qryTimeNodes:
SELECT RecordID, theMonth, Start AS TimeNode, -1 As StartTimeNode FROM
MyTimeTable UNION SELECT RecordID, theMonth, End AS TimeNode, 1 As
StartTimeNode FROM MyTimeTable ORDER BY theMonth, TimeNode;
I tried just a running sum and convinced myself that, although not fully
optimized, I have the means to get a solution within a reasonable amount
of time.
qryRunningSum:
SELECT theMonth, TimeNode, (SELECT SUM(A.StartTimeNode) FROM
qryTimeNodes AS A WHERE A.TimeNode <= qryTimeNodesPosted.TimeNode AND
A.theMonth = qryTimeNodesPosted.theMonth) AS RunningSumStartTimeNode
FROM qryTimeNodesPosted;
The first few records of qryRunningSum look like:
theMonth TimeNode RunningSumStartTimeNode
200412 12/29/04 1:30:00 AM -1
200412 12/29/04 2:15:01 AM -2
200412 12/29/04 3:15:01 AM -3
200412 12/29/14 4:15:01 AM -4
...
200412 1/2/05 4:30:18 PM -2
200412 1/2/05 4:30:18 PM -2
200412 1/2/05 4:45:18 PM -1
200412 1/2/05 11:45:20 PM 0
200501 1/1/05 1:30:00 AM -1
...
Using a running sum on StartTimeNode isn't very efficient. I'm starting
to understand the problem better now. One thing that I noticed from the
sample data is that since most of the intervals are multiply covered,
the number of times where the running sum is 0 should be small. When
the month is fully covered, the running sum is 0 only at the end. When
the count of the TimeNode values contributed by Start and End up to the
current value match up is where an uncovered duration can start. Maybe
I can get that information earlier. I can start to look for more
efficient ways to go about it rather than looking at all possible time
durations. I must say I was surprised at how quickly John Viescas'
query ran on the sample data. I think I can learn from John's example
even after I've optimized everything I can. This is a great problem.
James A. Fortune
MPAP...@FortuneJames.com
HSalim[MVP]
You are right, there is, in a sense, too much data.
all timeslots are fully covered and the only "open" times were at the two
ends of the data.
As you are ready to test the next phase, delete a few records - for example
DELETE * FROM Schedule
WHERE (((Schedule.Start)<=#2/28/2005 3:30:0#)
AND ((Schedule.End)>=#2/28/2005 3:45:0#));
You will delete 8 rows and now see that a time slot opens up
You can use Union All to suppress the "Disctinct" ness of a union query
I don't quite understand the goal of the running sum query. Are you saying
that the final value of 0 indicates that there are no umcovered spots?
HS
"James A. Fortune" <jimfo...@compumarc.com> wrote in message
news:uzxa95RT...@TK2MSFTNGP10.phx.gbl...
James A. Fortune
> James,
> You are right, there is, in a sense, too much data.
> all timeslots are fully covered and the only "open" times were at the two
> ends of the data.
>
> As you are ready to test the next phase, delete a few records - for example
> DELETE * FROM Schedule
> WHERE (((Schedule.Start)<=#2/28/2005 3:30:0#)
> AND ((Schedule.End)>=#2/28/2005 3:45:0#));
> You will delete 8 rows and now see that a time slot opens up
>
O.K.
> You can use Union All to suppress the "Disctinct" ness of a union query
Thanks. I'll keep that idea in mind.
>
> I don't quite understand the goal of the running sum query. Are you saying
> that the final value of 0 indicates that there are no umcovered spots?
The running sum query is one way to implement the balanced parentheses
analogy. The running sum query keeps track of the the number of Start
times without corresponding End times. A -5 indicates that five time
intervals cover the current one. The final value will always be 0
because all the "parentheses" close at the end of the month. Values
cannot go positive. Any 0 values other than at the end of the month
indicate an interval that is not covered. A 0 value only at the end
means that every interval was covered. My point was that I think I can
do something more efficient than the running sum query.
>
> HS
James A. Fortune
MPAP...@FortuneJames.com