SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Averaging Monthly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 09/20/2012 :  18:36:53  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/20/2012 :  23:16:25  Show Profile  Reply with Quote
just group on MONTH(datefield) for monthly average

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

Go to Top of Page

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 10/10/2012 :  17:41:27  Show Profile  Reply with Quote
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

Edited by - sqlbug on 10/10/2012 18:48:40
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2012 :  23:46:08  Show Profile  Reply with Quote
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/

Go to Top of Page

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 10/11/2012 :  12:41:39  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 10/11/2012 :  15:08:24  Show Profile  Reply with Quote
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.

Edited by - sqlbug on 10/11/2012 15:11:31
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  15:19:02  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000