sum within range of datetimes? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

sum within range of datetimes?

Author

Topic

danno
Starting Member

4 Posts

Posted - 2006-12-13 : 15:00:05

Hello,

I have a question about summing values within a particular range of date times for some timeseries data I have in access. It's rainfall data, and the data are arranged by
TABLE: rain
---------------
ID datetime rainfall

Where each time 0.01 inch of rain falls, the datetime is recorded and rainfall = 1. rainfall always equals 1. So a bit of the table looks like:


ID   datetime           rainfall
1    7/6/2006 14:01:00  1
2    7/6/2006 14:05:00  1
3    7/8/2006 19:42:05  1

What I want to do is group all the data in regular 15-minute intervals. That is, tell me the sum of the rainfall values in each 15-minute block. Does this make sense? Is it possible? I've been fooling around with DateAdd, Sum, and GroupBy... something like this:


SELECT  DateAdd("n",datetime,15) AS Expr1, sum(rainfall) from rain group by DateAdd("n",datetime,15) ;

but that of course doesn't work. Any suggestions? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 15:05:21

SELECT DATEDIFF("n", 0, datetime) / 15, SUM(RainFall)
FROM Rain
GROUP BY DATEDIFF("n", 0, datetime) / 15

Peter Larsson
Helsingborg, Sweden

danno
Starting Member

4 Posts

Posted - 2006-12-13 : 15:10:29

OK, I've gotten this far to sum by hour, but I can't seem to get to 15-minute:

SELECT DatePart("y",datetime) AS jday, DatePart("yyyy",datetime) AS [year], datepart("h",datetime) as hour, Sum(rainfall) AS rainfall
FROM rain
GROUP BY DatePart("y",datetime), DatePart("yyyy",datetime), datepart("h",datetime);

danno
Starting Member

4 Posts

Posted - 2006-12-13 : 15:13:24

Wow, super quick reply. Many thanks, Peso.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 15:17:11

[code]SELECT Format("yyyymmdd", datetime) AS [date],
datediff("m", 0, datetime) / 15 as section,
sum(rainfall) AS rainfall
FROM rain
GROUP BY Format("yyyymmdd", datetime) AS [date],
datediff("m", 0, datetime) / 15[/code]

Peter Larsson
Helsingborg, Sweden

danno
Starting Member

4 Posts

Posted - 2006-12-13 : 15:46:45

Thank you Peso. Although ideally what I am looking for is for the data to be summed by 0, 15, 30, 45 minute groupings. for example, the output of the query would be something like:


datetime          sum rainfall
2006/07/07 10:00  2
2006/07/07 10:15  0
2006/07/07 10:30  1

which indicates the sum of rainfall in the past 15 minutes. Sorry if I was too vague to begin with. Maybe this is too much to ask of an SQL query... As you can see above, the furthest I've gotten is to the hour. Your code appears to group by 15 minutes, but not necessarily "even" 15-minute intervals (00, 15, 30, 45). Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 15:51:07

A SQL query would fix this, but since this is MS ACCESS...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources