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.

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


"John G" wrote:

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

GAH!!! OK...CrazyTotal still needs to be 9.00, I need a vacation....

"John G" wrote:

> 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
>
>
> "John G" wrote:
>
> > 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.

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


"John G" <(E-Mail Removed)> wrote in message
news:E00E69A7-DDE7-4F8A-933E-(E-Mail Removed)...
>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.

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" wrote:

> 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.
>
> "Michel Walsh" wrote:
>
> > 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
> >
> >
> >
> > "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
> > >
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:65825F7D-E64A-4F64-ADC5-(E-Mail Removed)...
> 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" wrote:
>
>> 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.
>>
>> "Michel Walsh" wrote:
>>
>> > 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
>> >
>> >
>> >
>> > "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
>> > news:(E-Mail Removed)...
>> > > 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
>> > >
>> > >
>> > >
>> >
>> >
>> >

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" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:65825F7D-E64A-4F64-ADC5-(E-Mail Removed)...
>> 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" wrote:
>>
>>> 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.
>>>
>>> "Michel Walsh" wrote:
>>>
>>> > 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
>>> >
>>> >
>>> >
>>> > "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
>>> > news:(E-Mail Removed)...
>>> > > 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
>>> > >
>>> > >
>>> > >
>>> >
>>> >
>>> >
>
>

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

> SELECT c.TicketID, c.start
> FROM table1 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

--
Kellie


"Michel Walsh" wrote:

> Hi,
>
>
>
> With
>
> 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
>
>
>
> 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 08: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
>
>
>
> The second query ( two typos in the original text, well one typo and one
> inversion):
>
> 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 08:00:00 1
> 123 8/12/2005 09:00:00 2
> 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 third and fourth queries,
>
> SELECT c.TicketID, c.start
> FROM table1 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
>
>
> as example, returns
>
> Query3
> TicketID start
> 123 8/12/2005 08:00:00
> 123 8/12/2005 14:00:00
> 123 8/13/2005 09:00:00
>
>
>
> and
>
>
> SELECT c.TicketID, c.stop
> FROM table1 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
>
>
> to get
>
> Query4
> TicketID stop
> 123 8/12/2005 12:00:00
> 123 8/12/2005 18:00:00
> 123 8/13/2005 11:00:00
>
>
>
>
>
> To "easily" glue back the last two queries, horizontally, we can use 3 more
> queries... yea!
>
>
> Query5:
>
> SELECT a.TicketID, a.start, COUNT(*) As rank
> FROM query3 As a INNER JOIN query3 As b
> ON a.ticketID=b.ticketID and a.start >= b.start
> GROUP BY a.ticketID, a.start
>
>
> Query6:
>
> SELECT a.TicketID, a.stop, COUNT(*) As rank
> FROM query4 As a INNER JOIN query4 As b
> ON a.ticketID=b.ticketID and a.stop >= b.stop
> GROUP BY a.ticketID, a.stop
>
>
>
> and our last query, the only one we will have to call, in fact, to make all
> the sequence running, transparently:
>
>
> Query7:
>
> SELECT Query5.TicketID, Query5.start, Query6.stop
> FROM Query5 INNER JOIN Query6
> ON (Query5.TicketID = Query6.TicketID)
> AND (Query5.rank = Query6.rank)
>
>
>
> Query7
> TicketID start stop
> 123 8/12/2005 08: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
>
>
>
>
>
> Hoping it may help
> Vanderghast, Access MVP
>
>
>

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


"Kellie" wrote:

> 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
>
> > SELECT c.TicketID, c.start
> > FROM table1 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
>
> --
> Kellie
>
>
> "Michel Walsh" wrote:
>
> > Hi,
> >
> >
> >
> > With
> >
> > 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
> >
> >
> >
> > 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 08: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
> >
> >
> >
> > The second query ( two typos in the original text, well one typo and one
> > inversion):
> >
> > 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 08:00:00 1
> > 123 8/12/2005 09:00:00 2
> > 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 third and fourth queries,
> >
> > SELECT c.TicketID, c.start
> > FROM table1 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
> >
> >
> > as example, returns
> >
> > Query3
> > TicketID start
> > 123 8/12/2005 08:00:00
> > 123 8/12/2005 14:00:00
> > 123 8/13/2005 09:00:00
> >
> >
> >
> > and
> >
> >
> > SELECT c.TicketID, c.stop
> > FROM table1 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
> >
> >
> > to get
> >
> > Query4
> > TicketID stop
> > 123 8/12/2005 12:00:00
> > 123 8/12/2005 18:00:00
> > 123 8/13/2005 11:00:00
> >
> >
> >
> >
> >
> > To "easily" glue back the last two queries, horizontally, we can use 3 more
> > queries... yea!
> >
> >
> > Query5:
> >
> > SELECT a.TicketID, a.start, COUNT(*) As rank
> > FROM query3 As a INNER JOIN query3 As b
> > ON a.ticketID=b.ticketID and a.start >= b.start
> > GROUP BY a.ticketID, a.start
> >
> >
> > Query6:
> >
> > SELECT a.TicketID, a.stop, COUNT(*) As rank
> > FROM query4 As a INNER JOIN query4 As b
> > ON a.ticketID=b.ticketID and a.stop >= b.stop
> > GROUP BY a.ticketID, a.stop
> >
> >
> >
> > and our last query, the only one we will have to call, in fact, to make all
> > the sequence running, transparently:
> >
> >
> > Query7:
> >
> > SELECT Query5.TicketID, Query5.start, Query6.stop
> > FROM Query5 INNER JOIN Query6
> > ON (Query5.TicketID = Query6.TicketID)
> > AND (Query5.rank = Query6.rank)
> >
> >
> >
> > Query7
> > TicketID start stop
> > 123 8/12/2005 08: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
> >
> >
> >
> >
> >
> > Hoping it may help
> > Vanderghast, Access MVP
> >
> >
> >