General discussion

Locked

SQL Query

By interstellar ·
In a simple table
(obsdate datetime, value money)

Rainfall records (values) are logged at the date and time the observations (obsdate). Since rain may occur more than once on one day a typical slice of data is:

2006-03-28 12:00:00.000 0.2
2006-03-29 14:45:00.000 0.2
2006-03-29 15:00:00.000 0.2
2006-03-29 15:15:00.000 0.4
2006-03-29 15:30:00.000 0.2
2006-03-29 19:00:00.000 0.2
2006-03-30 01:00:00.000 0.2

I need to know how much rainfall falls in one day. I can abstract distinct dates, but how to loop through these to accumulate the rainfall for each day. Any help much appreciated.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by john.a.wills In reply to SQL Query

In SQL there are no loops, but there are many set fucntions. What you need is to group by date and sum the rainfall:
select thedate, sum(rainfall)
from thetable
group by thedate;

Collapse -

by interstellar In reply to SQL Query

Many thanks - I was very nearly there but just couldn't get over the final hurdle.

I had tried summing but further to your suggestion, even grouping by the observed date, the initial file format was preserved even tho dates were identical for given single-days. What I needed to do was group by the converted date, hence arriving at the code:

declare @dailyrain TABLE (mydate datetime, myvalue money)

insert into @dailyrain(myvalue,mydate)
select sum(value),CONVERT(CHAR(11),obsdate,106)
from tbl_5314096SCREev_Rain as #1
where #1.value>0
group by CONVERT(CHAR(11),obsdate,106)
order by CONVERT(CHAR(11),obsdate,106)

select * from @dailyrain where 1=1

Collapse -

by joesherr In reply to SQL Query

From query by intersteller:
select sum(value),CONVERT(CHAR(11),obsdate,106)
from tbl_5314096SCREev_Rain as #1
where #1.value>0
group by CONVERT(CHAR(11),obsdate,106)
order by CONVERT(CHAR(11),obsdate,106)

In your convert function use 112 instead of 106.

This will cause it to sort by yyyymmdd instead of dd mon yyyy which would be a problem if your query covers more than one month

Collapse -

by interstellar In reply to SQL Query

An obscure feature of daily rain fall totals (dating from manual readings) is that readings begin at 09:00 and end at 08:45 the following day. Therefore a rainfall depth reading recorded at 08:45 should be included in the group-by for the previous date.

Any ideas how can I put a condition on the date?

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums