Count of records matching ranges in 2+ fields

[Killer42]

Hi all.

I dabble with Access, but haven't done anything in-depth for a number of years. What I want to do now is probably a fairly simple JOIN or something, but I just can't recall how to go about it. Or the right terminology to do a proper search. Hope someone can help. Oh, this is in Access 2003, by the way.

I have a table which includes start-time and end-time fields (field type is date/time). By setting up a parameter query I can easily have it prompt me for a given time (let's call it "T1"), and show the count of records which span the hour starting at that time, with criteria something like "[StartTime] >= T1 AND [EndTime] <= (T1 + 1hr)". Sorry about the syntax, but I'm not currently at the PC with Access installed and my SQL's a bit rusty.

This works OK for a single hour. But I want to list a breakdown of the records per hour. In other words, I should be able to

open a query (or whatever) and come up with something like...


Date/Time Count
01/01/2006 01:00 10
01/01/2006 02:00 28
.
.
.

Hopefully I won't need to be spoonfed - a budge in the right direction should suffice. Also, this is just for quick & dirty

use, doesn't matter if it's messy. I've actually generated another table containing records with nothing but the start and end time for each hour I'm interested in, in the hope that would help, but still can't recall (or find) how to join them up

to get my counts.

Ciao, and TIA.

[PEB]

Hi,

To obtain a group by date and hours you need The format function like:

Expr1: Format([Date_intr],"dd/mm/yyyy\ hh")

Best regards!

[Killer42]

Hi,
To obtain a group by date and hours you need The format function like:
Expr1: Format([Date_intr],"dd/mm/yyyy\ hh")
Best regards!

Thanks for that.

However, it doesn't quite cover the issue. Probably because I have difficulty explaining clearly what the issue is. But I need to group records by a couple of fields "centred around" a specific date/time. I have managed to get something like the sort of output I'm after, by creating a JOIN in the GUI then modifying the SQL. Here's what I've got so far. I'm still hoping someone can help improve on it, as this takes hours to run, and that's only with a relatively small sample of my data.

I think I mentioned this before, but as part of experimenting I have created a table called DateTime2 with fields StartTime and EndTime. These hold the starting and ending values for each hour. This is the first draft SQL I have used to count the records from the main table ("Log") which span any part of each hour...

SELECT DateTime2.StartTime, Count(Log.JobNum) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON (Log.Started <= DateTime2.EndTime
AND Log.Ended >= DateTime2.StartTime)
GROUP BY DateTime2.StartTime;

I have not yet determined whether the results generated are correct - only that it did generate results, and at first glance they look reasonable.

[MMcCarthy]

Your problem is in your join, this should be your criteria

SELECT DateTime2.StartTime, Count(Log.JobNum) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON <You need an ID common to both tables>
WHERE (Log.Started <= DateTime2.EndTime
AND Log.Ended >= DateTime2.StartTime)
GROUP BY DateTime2.StartTime;

Thanks for that.

However, it doesn't quite cover the issue. Probably because I have difficulty explaining clearly what the issue is. But I need to group records by a couple of fields "centred around" a specific date/time. I have managed to get something like the sort of output I'm after, by creating a JOIN in the GUI then modifying the SQL. Here's what I've got so far. I'm still hoping someone can help improve on it, as this takes hours to run, and that's only with a relatively small sample of my data.

I think I mentioned this before, but as part of experimenting I have created a table called DateTime2 with fields StartTime and EndTime. These hold the starting and ending values for each hour. This is the first draft SQL I have used to count the records from the main table ("Log") which span any part of each hour...

SELECT DateTime2.StartTime, Count(Log.JobNum) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON (Log.Started <= DateTime2.EndTime
AND Log.Ended >= DateTime2.StartTime)
GROUP BY DateTime2.StartTime;

I have not yet determined whether the results generated are correct - only that it did generate results, and at first glance they look reasonable.

[Killer42]

Your problem is in your join, this should be your criteria

SELECT DateTime2.StartTime, Count(Log.JobNum) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON <You need an ID common to both tables>
WHERE (Log.Started <= DateTime2.EndTime
AND Log.Ended >= DateTime2.StartTime)
GROUP BY DateTime2.StartTime;

Sorry, a common ID is not the answer, I do know that much. A JOIN may even have been a mistake in the first place. You see, the whole idea of using JOIN with the second table was just one thing I have tried to resolve the problem. It is actually a single table I'm interested in. I am trying to get a count of the number of records which have two date/time fields which span a given date/time. Or in this particular case, span a given period of one hour. In other words, entries with a start date/time and end date/time which indicate something was active during a given period of time.

I'll try to explain with an example. Let's say we're dealing with phone calls, and for each one we track the starting and ending time in two date/time fields. For each hour of the day (7:00-7:59, 8:00-8:59, etc.) I want to generate a count of the number of calls which "touched" that period. That is, they may have started or ended during that period, or even been "active" over the entire period. Given one particular time, I can easily come up with a query which will ask Access to produce a count "...WHERE (StartTime >= Entered_Time) AND (EndTime < One_Hour_Later);".

This is fine if I am prompting for a time and producing one count. But what I need is to generate this sort of breakdown for the entire table, which has millions of entries. (Note, obviously records will often be counted more than once, since they will run over the end of a time period).

Sorry, but I just don't know how to express the problem any more clearly.

[MMcCarthy]

I'm assuming started and Ended are numbers (doubles)

SELECT Count(JobNum) AS CountOfJobs
FROM Log
WHERE (Started <= [Forms]![FormName]![StartTimeControlName]
AND Ended >= [Forms]![FormName]![StartTimeControlName])
OR (Started BETWEEN [Forms]![FormName]![StartTimeControlName] AND [Forms]![FormName]![StartTimeControlName] +1)
OR (Ended BETWEEN [Forms]![FormName]![StartTimeControlName] AND [Forms]![FormName]![StartTimeControlName] +1));

To do what you want to do it would be best to have a form for the user to enter the start time.

Sorry, a common ID is not the answer, I do know that much. A JOIN may even have been a mistake in the first place. You see, the whole idea of using JOIN with the second table was just one thing I have tried to resolve the problem. It is actually a single table I'm interested in. I am trying to get a count of the number of records which have two date/time fields which span a given date/time. Or in this particular case, span a given period of one hour. In other words, entries with a start date/time and end date/time which indicate something was active during a given period of time.

I'll try to explain with an example. Let's say we're dealing with phone calls, and for each one we track the starting and ending time in two date/time fields. For each hour of the day (7:00-7:59, 8:00-8:59, etc.) I want to generate a count of the number of calls which "touched" that period. That is, they may have started or ended during that period, or even been "active" over the entire period. Given one particular time, I can easily come up with a query which will ask Access to produce a count "...WHERE (StartTime >= Entered_Time) AND (EndTime < One_Hour_Later);".

This is fine if I am prompting for a time and producing one count. But what I need is to generate this sort of breakdown for the entire table, which has millions of entries. (Note, obviously records will often be counted more than once, since they will run over the end of a time period).

Sorry, but I just don't know how to express the problem any more clearly.

[Killer42]

I'm assuming started and Ended are numbers (doubles)

SELECT Count(JobNum) AS CountOfJobs
FROM Log
WHERE (Started <= [Forms]![FormName]![StartTimeControlName]
AND Ended >= [Forms]![FormName]![StartTimeControlName])
OR (Started BETWEEN [Forms]![FormName]![StartTimeControlName] AND [Forms]![FormName]![StartTimeControlName] +1)
OR (Ended BETWEEN [Forms]![FormName]![StartTimeControlName] AND [Forms]![FormName]![StartTimeControlName] +1));

To do what you want to do it would be best to have a form for the user to enter the start time.

You've identified the crux of the problem. As I stated in the original post, it is very easy to do this using a form to prompt for a date/time. What I need is to extend this to a breakdown of the entire table, so that I get a count for each hour (or month, minute, whatever) for the entire period.

Oh, and I'm dealing entirely with date/time fields/variables, not doubles. But the principle is about the same, I guess.

Incidentally, I believe your logic is more complex than required. For the sake of argument, let's say we have two fields S1 and E1. We want to catch any overlap with the range S2 to E2. If I read it correctly, your logic says something like...
WHERE ( ( (S1 <= S2) AND (E1 >= S2) )
OR (S1 BETWEEN S2 AND E2)
OR (E1 BETWEEN S2 AND E2) )
This makes sense, as you are checking for anything which spans the entire range(?), or starts within it, or ends within it. However, I'd suggest one need only check...
WHERE ( (S1 <= E2) AND (E1 >= S2) )

Oh, one other thing. I realise you were just illustrating a principle, but just out of curiosity, adding 1 to a date/time value would add what, a day?

[MMcCarthy]

Yes adding 1 to a date would add a day

Hour(#09/22/2006 10:42:27#) would return 22 (10 in 24 hour clock)

Therefore

Hour(#09/22/2006 10:42:27#)+1 would return 23

So if you use something like this

SELECT Sum(IIf(Hour([DateFieldName])=1,1,0) As CountHour1,
Sum(IIf(Hour([DateFieldName])=2,1,0) As CountHour2,

<repeat for all 24 hours>

FROM TableName;

I've used sum instead of count as it adds 1 for each valid date in each category.

You've identified the crux of the problem. As I stated in the original post, it is very easy to do this using a form to prompt for a date/time. What I need is to extend this to a breakdown of the entire table, so that I get a count for each hour (or month, minute, whatever) for the entire period.

Oh, and I'm dealing entirely with date/time fields/variables, not doubles. But the principle is about the same, I guess.

Incidentally, I believe your logic is more complex than required. For the sake of argument, let's say we have two fields S1 and E1. We want to catch any overlap with the range S2 to E2. If I read it correctly, your logic says something like...
WHERE ( ( (S1 <= S2) AND (E1 >= S2) )
OR (S1 BETWEEN S2 AND E2)
OR (E1 BETWEEN S2 AND E2) )
This makes sense, as you are checking for anything which spans the entire range(?), or starts within it, or ends within it. However, I'd suggest one need only check...
WHERE ( (S1 <= E2) AND (E1 >= S2) )

Oh, one other thing. I realise you were just illustrating a principle, but just out of curiosity, adding 1 to a date/time value would add what, a day?