Need a date query hero

J

John G

I think I figured it out using your sample data. (I think the numbers are
correct anyhow) I substituted queries AllStart and AllStop for 1 and 2 and
KeepStart and KeepsStop for 3 and 4. If this isn't how it was supposed to
work please let me know.
The issue that I am experiencing now is that I have employees that work
graveyard shifts and thier shift goes from say 08/13/2005 10:00 PM to
08/14/2005 7:00 AM the data is getting mucked up somewhere and I'm getting
negative numbers returned.

Currently my table looks like this:
"ID" "TicketID" "Start" "Stop"
1 123 8/12/2005 8:00:00 8/12/2005 11:00:00
2 123 8/12/2005 9:00:00 8/12/2005 12:00:00
3 123 8/12/2005 14:00:00 8/12/2005 18:00:00
4 123 8/13/2005 9:00:00 8/13/2005 11:00:00
6 123 8/12/2005 8:00:00 8/12/2005 12:00:00
12 123 8/12/2005 14:00:00 8/12/2005 18:00:00
13 123 8/12/2005 8:00:00 8/12/2005 11:00:00
14 123 8/13/2005 9:00:00 8/13/2005 13:00:00
15 123 8/14/2005 22:00:00 8/15/2005 07:00:00

The output is looking like this:
"TicketID" "Start" "Stop" "Total"
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 11:00:00 "2.00"
123 8/14/2005 9:00:00 8/13/2005 13:00:00 "-20.00"

Thanks,
John G
 
J

John G

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

John G

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.
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
K

Kellie

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
Click to expand...

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
Click to expand...



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

Kellie

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

Similar Threads

How to build subquery on aggregate query 0
Total Minutes Worked Within IVR Timing 1
Grouping/Deleting Dates/Times (HELP ANYONE!) 2
View Null Results in Query 1
Calculate the travel time hours 4
Calculate hours worked outside regular shift using date/time and iff condition 3
15 minute data to daily data 1
Crostab query for Summary report 2

Top