Count records in 10 minute time period

[StuJol]

using access 2003 is have the following query

SELECT [AlarmData Table Filtered by Date].[Event Type], [AlarmData Table
Filtered by Date].State, [AlarmData Table Filtered by Date].[Date/Time*]
FROM [AlarmData Table Filtered by Date]
GROUP BY [AlarmData Table Filtered by Date].[Event Type], [AlarmData Table
Filtered by Date].State, [AlarmData Table Filtered by Date].[Date/Time*]
HAVING ((([AlarmData Table Filtered by Date].[Event Type])="ALARM") AND
(([AlarmData Table Filtered by Date].State)="ACT/UNACK"));

this gives me something like

Event Type State Date/Time*
ALARM ACT/UNACK 19/01/2010 00:04:12
ALARM ACT/UNACK 19/01/2010 00:06:54
ALARM ACT/UNACK 19/01/2010 00:11:59
ALARM ACT/UNACK 19/01/2010 00:42:29
ALARM ACT/UNACK 19/01/2010 00:49:06
ALARM ACT/UNACK 19/01/2010 00:54:08
ALARM ACT/UNACK 19/01/2010 01:00:00
ALARM ACT/UNACK 19/01/2010 01:09:25
ALARM ACT/UNACK 19/01/2010 01:10:46

what i need to do is to count the number of entries and group them within a
10 minute time period. so for example

19/01/2010 00:10:00 total 5
19/01/2010 00:20:00 total 21
19/01/2010 00:30:00 total 2

any help would be great.. many thanks...

 

[Krzysztof Naworyta]

StuJol wrote:
| using access 2003 is have the following query
|
| SELECT [AlarmData Table Filtered by Date].[Event Type], [AlarmData
| Table Filtered by Date].State, [AlarmData Table Filtered by
| Date].[Date/Time*] FROM [AlarmData Table Filtered by Date]
| GROUP BY [AlarmData Table Filtered by Date].[Event Type], [AlarmData
| Table Filtered by Date].State, [AlarmData Table Filtered by
| Date].[Date/Time*] HAVING ((([AlarmData Table Filtered by
| Date].[Event Type])="ALARM") AND (([AlarmData Table Filtered by
| Date].State)="ACT/UNACK"));
|
| this gives me something like
|
| Event Type State Date/Time*
| ALARM ACT/UNACK 19/01/2010 00:04:12
| ALARM ACT/UNACK 19/01/2010 00:06:54
| ALARM ACT/UNACK 19/01/2010 00:11:59
| ALARM ACT/UNACK 19/01/2010 00:42:29
| ALARM ACT/UNACK 19/01/2010 00:49:06
| ALARM ACT/UNACK 19/01/2010 00:54:08
| ALARM ACT/UNACK 19/01/2010 01:00:00
| ALARM ACT/UNACK 19/01/2010 01:09:25
| ALARM ACT/UNACK 19/01/2010 01:10:46
|
| what i need to do is to count the number of entries and group them
| within a 10 minute time period. so for example
|
| 19/01/2010 00:10:00 total 5
| 19/01/2010 00:20:00 total 21
| 19/01/2010 00:30:00 total 2


SELECT
[Event Type],
State,
TimeSerial(Hour([Date/Time*]), (Minute([Date/Time*])\10)*10, 0) as t,
count(*) as tot
FROM [AlarmData Table Filtered by Date]
GROUP BY
[Event Type],
State,
TimeSerial(Hour([Date/Time*]), (Minute([Date/Time*])\10)*10, 0)
HAVING
[Event Type]="ALARM"
AND
State="ACT/UNACK"

 

[StuJol]

many thanks, looks good. the only comment is that it groups the dates. so for
time period 00:10 i get a total of 14 records, but 2 of these records occur
on day 1, 4 on day 2 and so on, so can we sort the query by date then 10 min
time scales.

appreciate your response. thank you.

 

[John W. Vinson]

many thanks, looks good. the only comment is that it groups the dates. so for
time period 00:10 i get a total of 14 records, but 2 of these records occur
on day 1, 4 on day 2 and so on, so can we sort the query by date then 10 min
time scales.

Sure, just include the date in the grouping level:

SELECT
[Event Type],
State,
DateValue([Date/Time*]) as d,
TimeSerial(Hour([Date/Time*]), (Minute([Date/Time*])\10)*10, 0) as t,
count(*) as tot
FROM [AlarmData Table Filtered by Date]
GROUP BY
[Event Type],
State,
DateValue([Date/Time*]),
TimeSerial(Hour([Date/Time*]), (Minute([Date/Time*])\10)*10, 0)
WHERE
[Event Type]="ALARM"
AND
State="ACT/UNACK"

I also changed the HAVING clause (which applies after all the grouping and
totalling is done) with a WHERE clause (which filters the records first).

 

[Krzysztof Naworyta]

John W. Vinson wrote:
| On Tue, 27 Apr 2010 07:34:01 -0700, StuJol
|
|| many thanks, looks good. the only comment is that it groups the
|| dates. so for time period 00:10 i get a total of 14 records, but 2
|| of these records occur on day 1, 4 on day 2 and so on, so can we
|| sort the query by date then 10 min time scales.
||
|
| Sure, just include the date in the grouping level:
|
| SELECT
| [Event Type],
| State,
| DateValue([Date/Time*]) as d,
| TimeSerial(Hour([Date/Time*]), (Minute([Date/Time*])\10)*10, 0) as
| t, count(*) as tot
| FROM [AlarmData Table Filtered by Date]
| GROUP BY
| [Event Type],
| State,
| DateValue([Date/Time*]),
| TimeSerial(Hour([Date/Time*]), (Minute([Date/Time*])\10)*10, 0)
| WHERE
| [Event Type]="ALARM"
| AND
| State="ACT/UNACK"


(...)
WHERE ...
GROUP BY ...

not
GROUP BY ...
WHERE ...




| I also changed the HAVING clause (which applies after all the
| grouping and totalling is done) with a WHERE clause (which filters
| the records first).

There is not big difference between WHERE and HAVING while filtering fields
after GROUP BY clause. Jet optimizes it somehow.
Sometimes HAVING is a lot faster, in correlated subqueries for example...