Hi,
ah ah... Untested. The easiest solution I think that can solve that problem,
in general, requires temporary queries. The first one:
SELECT TicketID, Start As Timing, +1 As offset FROM myTable
UNION ALL
SELECT TicketID, Stop, -1 FROM myTable
Observe then that the Start time it to be kept if the running sum of the
offset (against the increasing value of Timing) is +1, and the Stop time is
to be kept if that same running sum correspond to a running sum of 0.
Get that running sum (query2):
SELECT a.TicketID, a.Timing, SUM(b.offset) As Running
FROM query1 As a INNER JOIN query1 As b
ON a.ticketID = b.ticketID AND b.Timing >= a.Timing
GROUP BY a.TickerID, a.Timing
So, we only have to pick the required values:
SELECT c.TicketID, c.start
FROM myTable As c INNER JOIN query2 as d
ON c.ticketID= d.ticketid AND c.start = d.timing
WHERE d.running = 1
ORDER BY c.TicketID, c.start
for the starting values, and
SELECT c.TicketID, c.stop
FROM myTable As c INNER JOIN query2 as d
ON c.ticketID= d.ticketid AND c.stop = d.timing
WHERE d.running = 0
ORDER BY c.TicketID, c.stop
supplies the stopping values, for the merged (or not merge) intervals.
Hoping it may help,
Vanderghast, Access MVP
John G said:
Hi Michel, I apologize if I seem slow but I think my wires got crossed
somewhere and I explained what I was looking for poorly. What I am after
is a
query that will take the date/times from the table below and treat the
overlapping date/times as one record with the earliest date/time and the
lastest date/time of the overlapping records combimed into one.
So the table looks exactly like this:
ID Ticket# Techs Start Stop
1 123 1 08/12/2005 8:00 08/12/2005 11:00
2 123 2 08/12/2005 9:00 08/12/2005 12:00
3 123 1 08/12/2005 14:00 08/12/2005 18:00
4 123 1 08/13/2005 9:00 08/13/2005 11:00
the query I'm looking for needs to return this:
Ticket# Start Stop Total
123 08/12/2005 8:00 08/12/2005 12:00 4.00 <---combination of overlapping
times ID 1 and 2
123 08/12/2005 14:00 08/12/2005 18:00 2.00
123 08/13/2005 9:00 08/13/2005 11:00 2.00
The total field I got using this expression which works just fine:
Total: Format(((DateDiff("n",[Start],[Stop])/60)),"Fixed")
I just can't for the life of me figure out how to combine the overlapping
times.
Any ideas?
Michel Walsh said:
Hi,
You are not supposed to format the data, BEFORE making the
computation,
no.
The solution was based on the fact that:
L1-a1 + L2-a2 + L3-a3 + ..... + L9-a9
(ie, leaving minus arriving ) is the same as
( L1 + L2 + L3 + .... + L9 ) - ( a1 + a2 + a3 + ... + a9 )
which is the same as
SUM(L) - SUM(a)
When you say it return 27 (hours) and return 21, are you sure it really
SHOULD return 27.
Now, if your data exceed 24 hour, you may get a result as 31st
December
1899 01:00:00 instead of 25:00:00, as example. That, you have to
format
to get 25:00:00 rather than this date.
Int( 1E-5 + 24 * x ) & Format( x, ":nn:ss")
for x a positive period of time interval should do the job.
Hoping it may help,
Vanderghast, Access MVP
Thanks Michel,
Thank you for the quick reply. I am storing the dates with the times.
The example data was part of a query with an added calculated field to
give
the TotalMH. TotalMH is
Format(((DateDiff("n",[Start],[Stop])/60)*[Techs]),"Fixed"). This is
without
grouping.
Then in qryJobMH I grouped by Ticket# and then used
Sum(IIf(qryJob!TotalMH<>"",qryJob!TotalMH,"0.00")) AS TotalMH (*I
should
probably use a different name like TotalJobMH) for that query.
I'm pretty new at this and am not really sure if the query you provided
will
be able to do what I need it to. For instance when I ran it and say
increased
ID 2 to have 9 Techs the total should be 27 and the query returned only
21:00. Do I need to convert the returned data before I format it for
display?
Thanks for the help!
:
Hi,
Always store the date with the time, and then it seems to be a
matter
to
subtract SUMs:
SELECT id, SUM(Stop)-SUM(Start) As SumOfElapsed
FROM myTable
WHERE Not ( Stop IS NULL)
GROUP BY id
The WHERE clause removes records with incomplete (utilization)
information.
Hoping it may help
Vanderghast, Access MVP
I have a table that has 5 fields:
ID, AutoNumber
Ticket, Number
Techs, Number
Start, Date/Time
Stop, Date/Time
I built 3 seperate queries that output the below information
qryJob:
ID Ticket# Techs Start Stop TotalMH
1 A123 1 8/12/05 9:00 8/12/05 11:00 2.00
2 A123 2 8/12/05 9:00 8/12/05 12:00 6.00
3 A123 1 8/12/05 14:00 8/12/05 18:00 2.00
4 A123 1 8/13/05 9:00 8/13/05 11:00 2.00
I built another query that calculates the Total Man Hours for each
ticket
qryJobMH:
Ticket# TotalMH
A123 14.00
I also have yet another query that calculates the total elapsed time
qryJobElpsd:
Ticket# TotalElpsd
A123 26.00
I REALLY REALLY NEED HELP WITH a query that will output the below
information
Ticket# CrazyTotal
A123 9.00
This is essentially total elapsed time this ticket was being worked
on.
Earliest start time on 8/12 is 9:00 and then the latest end time for
that
bit
of overlap is 12:00 which is 3 hours. Then we have a gap of 2 hours
and
work
again from 14:00 to 18:00 which is an additional 4. The next day we
work
from
9:00 to 11:00 which is another 2. Add them up and we get 9.00 hours.
Any takers?
TIA!!!!
John G.
