If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count records in 10 minute time period
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... |
#2
|
|||
|
|||
Count records in 10 minute time period
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" -- KN |
#3
|
|||
|
|||
Count records in 10 minute time period
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. "Krzysztof Naworyta" wrote: 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" -- KN . |
#4
|
|||
|
|||
Count records in 10 minute time period
On Tue, 27 Apr 2010 07:34:01 -0700, StuJol
wrote: 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). -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Count records in 10 minute time period
John W. Vinson wrote:
| On Tue, 27 Apr 2010 07:34:01 -0700, StuJol | wrote: | || 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... -- KN |
Thread Tools | |
Display Modes | |
|
|