Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help on grouping by TIME Range 4

Status
Not open for further replies.
Idmpc

Idmpc

Programmer
Jun 25, 2008
16
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Amazing! Can't believe it was so simple!

Thank you very much for your help PHV!
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
The CASE expression is not supported by JetSQL.
Use the IIf function instead.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
Code: 
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

datajockey123
  • Locked
  • Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
318
dhookom
dhookom
WimDC
  • Locked
  • Question
Help on grouping by TIME Range
Replies
1
Views
139
dhookom
dhookom
btamsgn
  • Locked
  • Question
How to arrange the values in column heading based on column STT
Replies
10
Views
865
combo
combo
supportsvc
  • Locked
  • Question
Date Range within Date?
2
Replies
28
Views
464
Andrzejek
Andrzejek
serino
  • Locked
  • Question
Help with SQL inner join statement 1
Replies
2
Views
161
serino
serino

Part and Inventory Search

Sponsor

Back
Top