Grouping by Hour

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi all,

I have a query that groups by the hour. Is there a way to have to have it
group in 15 minute segments?

SELECT DatePart("h",[DMS1]) AS [Hour], Avg(qrDateDiff.Time) AS AvgOfTime
FROM qrDateDiff
GROUP BY DatePart("h",[DMS1]);
 
How about:

Datepart("h", [DMS1]), (Datepart("n", [DMS1]) -1)\15

you would need to include the second part in the SELECT clause as well, and
would give you something like the following. You might want to rework the
Select part so that the segment returns something a little more meaningful
(I'd probably go with a Choose( ) function, with the datepart calculation as
the expression, and something like "0-15", "16-30", "31-45", "46-60" as the
other arguments

H Segment AvgOfTime
1 0
1 1
1 2
1 3


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Hi all,

I have a query that groups by the hour. Is there a way to have to have it
group in 15 minute segments?

SELECT DatePart("h",[DMS1]) AS [Hour], Avg(qrDateDiff.Time) AS AvgOfTime
FROM qrDateDiff
GROUP BY DatePart("h",[DMS1]);
Click to expand...

Yep... since there are 96 (24x4) fifteen-minute segments in a day. Try

SELECT Int(CDbl(TimeValue([DMS1]) * 96)) AS TimeSeg, Avg(qrDateDiff.Time) AS
AvgOfTime FROM qrDateDiff GROUP BY Int(CDbl(TimeValue([DMS1]) * 96)) ;

Note that Time is a reserved word and a bad choice for a fieldname - Access
can and will confuse it with the builtin Time() function.
 
Back
Top