Averaging Monthly - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Averaging Monthly

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-09-20 : 18:36:53

I have these tables with hourly data values. The 2 main tables here are:

SAMPLE_DATA (SAMPLE_NUM(PK),SAMPLE_TYPE,SAMPLE_STATION,SAMPLE_START_DATE,
SAMPLE_START_TIME,SAMPLE_END_DATE,SAMPLE_END_TIME)
and,
STATION_DATA
(DATA_SAMPLE_NUM(FK),DATA_PARAMETER,DATA_METHOD,DATA_UNIT,DATA_VALUE)

What I need to do is - calculate monthly average for Each Station, Each Parameter and Each Method - for Selected Date range, Station(s) and Parameter(s).
This - for example will work for daily average for one Station/One Parameter:
Select STN_ID, STN_PLOTSITE_ID, STN_PLOT_ID, SAMPLE_TYPE, '7', DATA_PARAMETER,
DATA_METHOD, DATA_UNIT,SAMPLE_START_DATE, SAMPLE_END_DATE, AVG(DATA_VALUE) AS DAILY_AVERAGE
FROM STATION_INFO
INNER JOIN SAMPLE_DATA ON STN_ID = SAMPLE_STATION
INNER JOIN STATION_DATA ON SAMPLE_NUM = DATA_SAMPLE_NUM
AND DATA_PARAMETER = '81104' AND SAMPLE_STATION = 1028
AND SAMPLE_START_DATE >= @stDate AND SAMPLE_END_DATE <= @endDate
GROUP BY STN_ID, STN_PLOTSITE_ID, STN_PLOT_ID, SAMPLE_TYPE, SAMPLE_START_DATE,SAMPLE_END_DATE,SAMPLE_START_TIME,SAMPLE_END_TIME,
DATA_PARAMETER, DATA_METHOD, DATA_UNIT
HAVING SAMPLE_START_TIME >= 0 AND SAMPLE_END_TIME <=2359

But having hard time for monthly average.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:16:25

just group on MONTH(datefield) for monthly average

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-10-10 : 17:41:27

There are 2 problems.
1) There are 2 date fields (Start and End Dates), we can only group by the MONTH(SAMPLE_START_DATE) - but how do I fix #2?

2) It complains about SAMPLE_START_DATE not being in the SELECT list because I used MONTH(SAMPLE_START_DATE) instead of just SAMPLE_START_DATE. If I include SAMPLE_START_DATE also in the group by, then it doesn't group by Month anymore obviously.

Thanks Visakh.

sqlbug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-10 : 23:46:08

quote:
Originally posted by sqlbug

There are 2 problems.
1) There are 2 date fields (Start and End Dates), we can only group by the MONTH(SAMPLE_START_DATE) - but how do I fix #2?

2) It complains about SAMPLE_START_DATE not being in the SELECT list because I used MONTH(SAMPLE_START_DATE) instead of just SAMPLE_START_DATE. If I include SAMPLE_START_DATE also in the group by, then it doesn't group by Month anymore obviously.

Thanks Visakh.

sqlbug

if you're grouping on MONTH level whats the significance of date in output? As obviously you'll have lot of date values within the month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-10-11 : 12:41:39

Thats a good point.
I just wanted the Beginning (date) of the month as the start date and end (date) of the month as end date.
Thanks.

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-10-11 : 15:08:24

I got it solved. I got the start and end dates for each months in the date range in a temp table, then joined it with my query - grouping by the start/end dates.
Really appreciate you helping me in lot of my issues.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 15:19:02

quote:
Originally posted by sqlbug

Thats a good point.
I just wanted the Beginning (date) of the month as the start date and end (date) of the month as end date.
Thanks.

you mean absolute beginning date and end date of month
or earliest and latest date present in your table for the month?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources