Need a date query hero

G

Guest

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

Guest

Sorry, I just realized that I posted some flawed math...the TotalMH for ID 3
is supposed to be 4.00 and not 2.00. So the CrazyTotal would need to be
11.00, not 9.00
:)
 
M

Michel Walsh

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
 
G

Guest

So, after much testing I've discovered some issues with both of the above
approaches. I did not however give up and managed to find a post from Bryan
Baker back in 2003 with something that did the trick (except it doesn't
remove exact duplicates but I've sorta figured out a way around it which I
outline below):
So....

tblJob -
ID, Autonumber, key
TicketID, Long
Techs, Long
Start, Date/Time
Stop, Date/Time

SAMPLE DATA
tblJob:
"ID" "TicketID" "Techs" "Start" "Stop"
1 123 1 8/12/2005 8:00:00 8/12/2005 11:00:00
2 123 2 8/12/2005 9:00:00 8/12/2005 12:00:00
3 123 1 8/12/2005 14:00:00 8/12/2005 18:00:00
4 123 2 8/12/2005 8:00:00 8/12/2005 12:00:00
5 123 1 8/12/2005 8:00:00 8/12/2005 11:00:00
6 123 1 8/13/2005 9:00:00 8/13/2005 11:00:00
7 123 1 8/13/2005 9:00:00 8/13/2005 13:00:00
8 123 1 8/14/2005 22:00:00 8/15/2005 7:00:00

So, from the above data the goal was to get this output through a query:

"TicketID" "Start" "Stop" "TotalHours"
123 8/12/2005 8:00:00 8/12/2005 12:00:00 "4.00"
123 8/12/2005 14:00:00 8/12/2005 18:00:00 "4.00"
123 8/13/2005 9:00:00 8/13/2005 13:00:00 "4.00"
123 8/14/2005 22:00:00 8/15/2005 7:00:00 "9.00"

The queries are named Test1, Test2 and Test3 as follows

Test1:
SELECT O1.TicketID, O1.Start, Min(O2.Stop) AS Stop
FROM tblJob AS O1, tblJob AS O2
WHERE (((O1.TicketID)=[O2].[TicketID]) AND ((O2.Stop)>=[O1].[Stop]) AND
((Exists (SELECT * FROM tblJob AS O3
WHERE O3.Start < O1.Start
AND O3.Stop >= O1.Start
AND O3.TicketID = O1.TicketID
))=False) AND ((Exists (SELECT * FROM tblJob O3
WHERE O3.Stop > O2.Stop
AND O3.Start <= O2.Stop
AND O3.TicketID = O2.TicketID
))=False))
GROUP BY O1.TicketID, O1.Start;

Test2:
SELECT Test1.TicketID, Test1.Start, Test1.Stop
FROM Test1 INNER JOIN tblJob AS O ON (Test1.TicketID=O.TicketID) AND
(Test1.Stop=O.Stop)
WHERE (((Exists (select * from tblJob AS O2 where Test1.TicketID =
O2.TicketID and Test1.Stop = O2.Stop and O2.Start > O.Start ))=False))
ORDER BY Test1.TicketID, Test1.Start;

Test3:
SELECT tblJob.TicketID, Test2.Start, Test2.Stop,
Format((DateDiff("n",[Test2].[Start],[Test2].[Stop])/60),"Fixed") AS
TotalHours
FROM tblJob LEFT JOIN Test2 ON tblJob.TicketID = Test2.TicketID
GROUP BY tblJob.TicketID, Test2.Start, Test2.Stop;

Test3 gives us exactly the above data we were looking for. The only thing as
I mentioned is that if there are exact duplicate records in the table the
output shows them. For instance if record ID 3 had an exact duplicate it
would would get listed with the output as well. If anyone can figure out how
to get rid of the duplicates in the queries above I'd love to hear it. I'm
using yet another query that does a select distinct from Test3 but I'm sure
there's gotta be a cleaner way. Also, if anyone cares to tidy these up or
see's anywhere there could be improvements speak up.
Thanks all!
 
M

Michel Walsh

Hi,



Seems to be an oversight of my part. Instead of COUNT(*)=1, need COUNT(*)<>
COUNT(b.start). With that modification, KeepStarts and KeepStops now produce
the right result under the reported circumstances:


SELECT a.TicketID, a.Start
FROM AllStarts AS a LEFT JOIN Table1 AS b ON (a.TicketID=b.TicketID) AND
(a.start>b.start) AND (a.start<=b.stop)
GROUP BY a.TicketID, a.Start
HAVING COUNT(*) <> COUNT(b.start)

KeepStarts
TicketID Start
123 2005.08.12 08:00:00
123 2005.08.12 14:00:00
123 2005.08.13 09:00:00
123 2005.08.14 09:00:00



and


SELECT a.TicketID, a.Stop
FROM AllStops AS a LEFT JOIN Table1 AS b ON (a.TicketID=b.TicketID) AND
(a.stop<b.stop) AND (a.stop>=b.start)
GROUP BY a.TicketID, a.Stop
HAVING COUNT(*) <> COUNT(b.start)

KeepStops
TicketID Stop
123 2005.08.12 12:00:00
123 2005.08.12 18:00:00
123 2005.08.13 13:00:00
123 2005.08.15 07:00:00





and we are left to merge horizontally these two results.



Hoping it may help,
Vanderghast, Access MVP



John G said:
So, after much testing I've discovered some issues with both of the above
approaches. I did not however give up and managed to find a post from
Bryan
Baker back in 2003 with something that did the trick (except it doesn't
remove exact duplicates but I've sorta figured out a way around it which I
outline below):
So....

tblJob -
ID, Autonumber, key
TicketID, Long
Techs, Long
Start, Date/Time
Stop, Date/Time

SAMPLE DATA
tblJob:
"ID" "TicketID" "Techs" "Start" "Stop"
1 123 1 8/12/2005 8:00:00 8/12/2005 11:00:00
2 123 2 8/12/2005 9:00:00 8/12/2005 12:00:00
3 123 1 8/12/2005 14:00:00 8/12/2005 18:00:00
4 123 2 8/12/2005 8:00:00 8/12/2005 12:00:00
5 123 1 8/12/2005 8:00:00 8/12/2005 11:00:00
6 123 1 8/13/2005 9:00:00 8/13/2005 11:00:00
7 123 1 8/13/2005 9:00:00 8/13/2005 13:00:00
8 123 1 8/14/2005 22:00:00 8/15/2005 7:00:00

So, from the above data the goal was to get this output through a query:

"TicketID" "Start" "Stop" "TotalHours"
123 8/12/2005 8:00:00 8/12/2005 12:00:00 "4.00"
123 8/12/2005 14:00:00 8/12/2005 18:00:00 "4.00"
123 8/13/2005 9:00:00 8/13/2005 13:00:00 "4.00"
123 8/14/2005 22:00:00 8/15/2005 7:00:00 "9.00"

The queries are named Test1, Test2 and Test3 as follows

Test1:
SELECT O1.TicketID, O1.Start, Min(O2.Stop) AS Stop
FROM tblJob AS O1, tblJob AS O2
WHERE (((O1.TicketID)=[O2].[TicketID]) AND ((O2.Stop)>=[O1].[Stop]) AND
((Exists (SELECT * FROM tblJob AS O3
WHERE O3.Start < O1.Start
AND O3.Stop >= O1.Start
AND O3.TicketID = O1.TicketID
))=False) AND ((Exists (SELECT * FROM tblJob O3
WHERE O3.Stop > O2.Stop
AND O3.Start <= O2.Stop
AND O3.TicketID = O2.TicketID
))=False))
GROUP BY O1.TicketID, O1.Start;

Test2:
SELECT Test1.TicketID, Test1.Start, Test1.Stop
FROM Test1 INNER JOIN tblJob AS O ON (Test1.TicketID=O.TicketID) AND
(Test1.Stop=O.Stop)
WHERE (((Exists (select * from tblJob AS O2 where Test1.TicketID =
O2.TicketID and Test1.Stop = O2.Stop and O2.Start > O.Start ))=False))
ORDER BY Test1.TicketID, Test1.Start;

Test3:
SELECT tblJob.TicketID, Test2.Start, Test2.Stop,
Format((DateDiff("n",[Test2].[Start],[Test2].[Stop])/60),"Fixed") AS
TotalHours
FROM tblJob LEFT JOIN Test2 ON tblJob.TicketID = Test2.TicketID
GROUP BY tblJob.TicketID, Test2.Start, Test2.Stop;

Test3 gives us exactly the above data we were looking for. The only thing
as
I mentioned is that if there are exact duplicate records in the table the
output shows them. For instance if record ID 3 had an exact duplicate it
would would get listed with the output as well. If anyone can figure out
how
to get rid of the duplicates in the queries above I'd love to hear it. I'm
using yet another query that does a select distinct from Test3 but I'm
sure
there's gotta be a cleaner way. Also, if anyone cares to tidy these up or
see's anywhere there could be improvements speak up.
Thanks all!

John G said:
Hi again Michel, I don't know if you had a chance to see my last post to
this
thread as I just realized I replied to myself. I think there is one last
obsticle to overcome which I outlined in my last post before this one.
I'm at
the point now that I would be more than willing to offer you some
compensation for a solution to this as I feel I have received much more
of
your time than I could have hoped for. Anyhow, let me know what your
thoughts
are and if you think a solution is attainable.

Thank you for everything,
John G.
 
M

Michel Walsh

Hi,


Can try to replace the unstandard HAVING in KeepStarts:

SELECT a.TicketID, a.Start
FROM AllStarts AS a LEFT JOIN Table1 AS b
ON (a.TicketID=b.TicketID) AND (a.start>b.start) AND (a.start<=b.stop)
GROUP BY a.TicketID, a.Start
HAVING COUNT(*) <> COUNT(b.start)


into a more standard, and simpler:

SELECT a.TicketID, a.Start
FROM AllStarts AS a LEFT JOIN Table1 AS b
ON (a.TicketID=b.TicketID) AND (a.start>b.start) AND (a.start<=b.stop)
WHERE b.start IS NULL



Both formulations are not logically equivalent, in general, but seem to,
here, produce the same results, in our case.

Hoping it may help,
Vanderghast, Access MVP

Michel Walsh said:
Hi,



Seems to be an oversight of my part. Instead of COUNT(*)=1, need
COUNT(*)<> COUNT(b.start). With that modification, KeepStarts and
KeepStops now produce the right result under the reported circumstances:


SELECT a.TicketID, a.Start
FROM AllStarts AS a LEFT JOIN Table1 AS b ON (a.TicketID=b.TicketID) AND
(a.start>b.start) AND (a.start<=b.stop)
GROUP BY a.TicketID, a.Start
HAVING COUNT(*) <> COUNT(b.start)

KeepStarts
TicketID Start
123 2005.08.12 08:00:00
123 2005.08.12 14:00:00
123 2005.08.13 09:00:00
123 2005.08.14 09:00:00



and


SELECT a.TicketID, a.Stop
FROM AllStops AS a LEFT JOIN Table1 AS b ON (a.TicketID=b.TicketID) AND
(a.stop<b.stop) AND (a.stop>=b.start)
GROUP BY a.TicketID, a.Stop
HAVING COUNT(*) <> COUNT(b.start)

KeepStops
TicketID Stop
123 2005.08.12 12:00:00
123 2005.08.12 18:00:00
123 2005.08.13 13:00:00
123 2005.08.15 07:00:00





and we are left to merge horizontally these two results.



Hoping it may help,
Vanderghast, Access MVP



John G said:
So, after much testing I've discovered some issues with both of the above
approaches. I did not however give up and managed to find a post from
Bryan
Baker back in 2003 with something that did the trick (except it doesn't
remove exact duplicates but I've sorta figured out a way around it which
I
outline below):
So....

tblJob -
ID, Autonumber, key
TicketID, Long
Techs, Long
Start, Date/Time
Stop, Date/Time

SAMPLE DATA
tblJob:
"ID" "TicketID" "Techs" "Start" "Stop"
1 123 1 8/12/2005 8:00:00 8/12/2005 11:00:00
2 123 2 8/12/2005 9:00:00 8/12/2005 12:00:00
3 123 1 8/12/2005 14:00:00 8/12/2005 18:00:00
4 123 2 8/12/2005 8:00:00 8/12/2005 12:00:00
5 123 1 8/12/2005 8:00:00 8/12/2005 11:00:00
6 123 1 8/13/2005 9:00:00 8/13/2005 11:00:00
7 123 1 8/13/2005 9:00:00 8/13/2005 13:00:00
8 123 1 8/14/2005 22:00:00 8/15/2005 7:00:00

So, from the above data the goal was to get this output through a query:

"TicketID" "Start" "Stop" "TotalHours"
123 8/12/2005 8:00:00 8/12/2005 12:00:00 "4.00"
123 8/12/2005 14:00:00 8/12/2005 18:00:00 "4.00"
123 8/13/2005 9:00:00 8/13/2005 13:00:00 "4.00"
123 8/14/2005 22:00:00 8/15/2005 7:00:00 "9.00"

The queries are named Test1, Test2 and Test3 as follows

Test1:
SELECT O1.TicketID, O1.Start, Min(O2.Stop) AS Stop
FROM tblJob AS O1, tblJob AS O2
WHERE (((O1.TicketID)=[O2].[TicketID]) AND ((O2.Stop)>=[O1].[Stop]) AND
((Exists (SELECT * FROM tblJob AS O3
WHERE O3.Start < O1.Start
AND O3.Stop >= O1.Start
AND O3.TicketID = O1.TicketID
))=False) AND ((Exists (SELECT * FROM tblJob O3
WHERE O3.Stop > O2.Stop
AND O3.Start <= O2.Stop
AND O3.TicketID = O2.TicketID
))=False))
GROUP BY O1.TicketID, O1.Start;

Test2:
SELECT Test1.TicketID, Test1.Start, Test1.Stop
FROM Test1 INNER JOIN tblJob AS O ON (Test1.TicketID=O.TicketID) AND
(Test1.Stop=O.Stop)
WHERE (((Exists (select * from tblJob AS O2 where Test1.TicketID =
O2.TicketID and Test1.Stop = O2.Stop and O2.Start >
O.Start ))=False))
ORDER BY Test1.TicketID, Test1.Start;

Test3:
SELECT tblJob.TicketID, Test2.Start, Test2.Stop,
Format((DateDiff("n",[Test2].[Start],[Test2].[Stop])/60),"Fixed") AS
TotalHours
FROM tblJob LEFT JOIN Test2 ON tblJob.TicketID = Test2.TicketID
GROUP BY tblJob.TicketID, Test2.Start, Test2.Stop;

Test3 gives us exactly the above data we were looking for. The only thing
as
I mentioned is that if there are exact duplicate records in the table the
output shows them. For instance if record ID 3 had an exact duplicate it
would would get listed with the output as well. If anyone can figure out
how
to get rid of the duplicates in the queries above I'd love to hear it.
I'm
using yet another query that does a select distinct from Test3 but I'm
sure
there's gotta be a cleaner way. Also, if anyone cares to tidy these up or
see's anywhere there could be improvements speak up.
Thanks all!

John G said:
Hi again Michel, I don't know if you had a chance to see my last post to
this
thread as I just realized I replied to myself. I think there is one last
obsticle to overcome which I outlined in my last post before this one.
I'm at
the point now that I would be more than willing to offer you some
compensation for a solution to this as I feel I have received much more
of
your time than I could have hoped for. Anyhow, let me know what your
thoughts
are and if you think a solution is attainable.

Thank you for everything,
John G.

:

Hi,


Here something that seems to work:

AllStarts
--------------
SELECT DISTINCT TicketID, Start
FROM Table1;


AllStops
------------
SELECT DISTINCT TicketID, Stop
FROM table1;


KeepStarts
------------------
SELECT a.TicketID, a.Start
FROM AllStarts As a LEFT JOIN Table1 As b
ON a.TicketID = b.TicketID AND a.start > b.start AND a.start <=
b.stop
GROUP BY a.TicketID, a.Start
HAVING COUNT(*)=1



KeepStops
----------------
SELECT a.TicketID, a.Stop
FROM AllStops As a LEFT JOIN Table1 As b
ON a.TicketID = b.TicketID AND a.stop < b.stop AND a.stop >= b.start
GROUP BY a.TicketID, a.Stop
HAVING COUNT(*)=1



It is then a matter to merge together KeepStarts and KeepStops, a
little bit
as we did before with query3 and query4.


I tried with the following set of data:

Table1
TicketID Start Stop
123 8/12/2005 08:00:00 8/12/2005 11:00:00
123 8/12/2005 09:00:00 8/12/2005 12:00:00
123 8/12/2005 14:00:00 8/12/2005 18:00:00
123 8/13/2005 09:00:00 8/13/2005 11:00:00
123 8/12/2005 08:00:00 8/12/2005 12:00:00



More tests are surely required.


Hoping it may help,
Vanderghast, Access MVP



Hi,


Indeed, I assumed Start and Stop time where all different. If that
assumption does not hold, the queries won't work.

I have to think about a solution when Start and Stop times can occur
multiple time.


Vanderghast, Access MVP
 
G

Guest

Hi Michael

I have applied the queries to my overlapping times and it all works great
until I have a case where the start times are the same ie 9:00
Table1
TicketID Start Stop
123 8/12/2005 09:00:00 8/12/2005 11:00:00
123 8/12/2005 09:00:00 8/12/2005 12:00:00
123 8/12/2005 14:00:00 8/12/2005 18:00:00
123 8/13/2005 09:00:00 8/13/2005 11:00:00

The first query
SELECT TicketID, Start As Timing, +1 As offset FROM table1
UNION ALL
SELECT TicketID, Stop, -1 FROM table1

returns


Query1
TicketID Timing offset
123 8/12/2005 09:00:00 1
123 8/12/2005 09:00:00 1
123 8/12/2005 14:00:00 1
123 8/13/2005 09:00:00 1
123 8/12/2005 11:00:00 -1
123 8/12/2005 12:00:00 -1
123 8/12/2005 18:00:00 -1
123 8/13/2005 11:00:00 -1




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.TicketID, a.Timing


returns

Query2
TicketID Timing Running
123 8/12/2005 09:00:00 4
123 8/12/2005 11:00:00 1
123 8/12/2005 12:00:00 0
123 8/12/2005 14:00:00 1
123 8/12/2005 18:00:00 0
123 8/13/2005 09:00:00 1
123 8/13/2005 11:00:00 0



The next query does not pick up the line because it is a 4 not a 1
 
G

Guest

Sorry ignore the question found the later response to this problem - I will
try this out
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top