Count records in 10 minute time period

  • Thread starter Thread starter StuJol
  • Start date Start date
S

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

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).
 
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...
 
Back
Top