Query Occurences of TimeValue 1

[Moebius01]

I'm having to try this in both Access and SQL Server (as we're in a bit of a licensing political battle).

Basically, I have a table Durations with fields Start and End (plus others irrelevant) both formatted as DateTime with a complete date and time in them. Basically, the table stores durations of transactions (start and end time).

What I need to determine is how many seconds of the day have more than 1 event vs how many seconds have only 1. So far, we came up with a Module to loop through every second of the day, and query the with IIf(Count(*)1,1,0) and IIf(Count(*)>1,1,0), then summing the results. The problem is this means running 86400 select queries (one for each second of a day) which is to say the least, time consuming.

I came across a suggestion to create a second table with a record for each second of the day and run a join, but this was in SQL and I'm not sure if I could achieve this level of join in Access.

Can anyone think of another way to run through this data to determine how many seconds were spent with more than 1 simultaneous event vs how many were spent with only 1?

 

[JonFer]

If your final result going to be how many seconds you had 1 event, how many seconds 2 events were processing? Like this:

1 event over 400 seconds
2 events over 150 seconds
3 events over 100 seconds, etc

You don't actually need to know the exact second of the day do you?

 

[JonFer]

Interesting problem. Try this:

1. Create a Union query to get each Start and End date:

   Select Distinct StartDate as Date1
   from TableEvents
   Union
   Select Distinct EndDate
   from TableEvents

2. Create a query (qryEventRanges) based on the Union to get all date range for whenever any event was occuring:

   Select Date1 as StartDate,
   (Select Min(Date1) from UnionQuery as a
    Where a.Date1>UnionQuery.Date1) as EndDate
   From UnionQuery

3. Join the qryEventRanges to your TableEvents:

   SELECT qryEventRanges.StartDate, 
          qryEventRanges.EndDate, 
          Count(*) AS Events
   FROM   TableEvents, qryEventRanges
   WHERE  qryEventRanges.StartDate>=[TableEvents].[StartDate] AND 
          qryEventRanges.EndDate<=[TableEvents].[EndDate]
   GROUP BY qryEventRanges.StartDate, qryEventRanges.EndDate;

Make sure all of that is working and then write a Group By query that Groups by the Events field above and Sums the seconds between StartDate and EndDate. You should get something like:

Events Secs
1 400
2 300
...

 

[Moebius01]

JonFer, thanks for getting my brain out of the "loop" I was stuck in! I had gotten so buried in trying to figure out a way to loop through each second that I lost the big picture there. I was able to work with your idea to get the results I need in an acceptable run time.

 

[JonFer]

Glad I could help - it took a while to think of a good approach.