Help on grouping by TIME Range 4

[Idmpc]

Hi everyone

I'm using MS Access
I have a table that has a field called "DT" of DateTime type, between others.
there's at least 3 or 4 records for each second, for example:

No. Record Field1 DT
1 AA 06/25/2008 15:10:01
2 BB 06/25/2008 15:10:01
3 CC 06/25/2008 15:10:01
4 DD 06/25/2008 15:10:01
5 EE 06/25/2008 15:10:02
6 FF 06/25/2008 15:10:02
7 GG 06/25/2008 15:10:02
8 HH 06/25/2008 15:10:03
9 JJ 06/25/2008 15:10:03
etc..

and the records go from 00:00:00 to 23:59:59 for about 7 days. (I have about 1.200.000+ records on the database)

What I want to do is to make a SQL Query that will organize these DateTime values in hour ranges. I want my SQL Query result to be the following:

Range Count
00:00:00 - 01:00:00 789
01:00:01 - 02:00:00 98
02:00:01 - 03:00:00 978
... ...
23:00:00 - 23:59:59 56

I want the "Count" field to show how many records are contained inside the range.

Please do note that, at first, I do not care about the dates, only the hours stored on the database, since I want to know the number of records inside those ranges, date independent. However, it would be nice if you could also show me how to count how many records are inside that range within a specified date.

I know that if I use the command Format (DT, "hh:nn:ss") I will retrieve only the Time part of the DateTime "DT" Field, but I have no idea of how to organize the counts in those ranges I need.

I've tried to create a new, temporary table to store the ranges and use JOIN somehow, but I'm not that much of an expert on SQL and I could get no results.

Can anyone help me?
Thanks in advance.

 

[PHV]

SELECT Format(DT,'hh:00:00 - hh:59:59') AS Range, Count(*) AS [Count]
FROM yourTable
GROUP BY Format(DT,'hh:00:00 - hh:59:59')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Idmpc]

Amazing! Can't believe it was so simple!

Thank you very much for your help PHV!

 

[Idmpc]

One more question:

Can I use CASE Statement to rename the values showed by Format(DT,'hh:00:00 - hh:59:59')?

I mean, instead of displaying 00:00:00 - 00:59:59 and so on, it could display any other text like "Range 1", Range 2", etc..

If I can do it with CASE, how would I use it? I've been trying to use CASE with SQL on Access for a while but I've been getting some syntax errors, since I'm not familiar with it.

Thanks again!

 

[PHV]

The CASE expression is not supported by JetSQL.
Use the IIf function instead.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Idmpc]

Thanks again!

I have one last question:

If I want to make those ranges custom, for example, instead of one in one hour, it can be from 2 in 2 hours, 3 in 3 hours, etc..

Is it possible?

Thank you once again

 

[Golom]

That's a bit harder.

First Build an Integers table with one field (N). Insert the numbers from 0 to 24 into the table.

Second Build a query

PARAMETERS [Interval] Long;
SELECT  H.Range, Count(*) As [Count]
FROM MyTable As M, 

(
SELECT Hour(DateAdd("h",I.N*[Interval],0)) AS FromHour,
       Hour(DateAdd("h",(I.N*[Interval])+[Interval]-1,0)) AS ToHour, 
       Format(DateAdd("h",I.N*[Interval],0),"hh:00:00")  & " - " &  
       Format(DateAdd("h",(I.N*[Interval])+[Interval]-1,0),"hh:59:59") AS Range

FROM Integers AS I

WHERE I.N < 24 / [Interval]
)

As H
Where Hour(M.DateField) Between H.FromHour and H.ToHour

Group By H.Range

This query will ask you for an Interval. Type the number of hours (e.g. 1, 2, 3, etc.) that you want and it will generate a result with that number of hours in each range. Note that if you enter a value that doesn't divide into 24 an integer number of times then you will get overlapping ranges.

 

[Idmpc]

Amazing.

Thanks for the help, Golom! After reading that code for a few minutes I managed to understand what's going on! Really nice code you wrote.

Thanks everyone for the major help!