| Author |
 Topic  |
|
|
sqlbug
Posting Yak Master
Canada
177 Posts |
 Posted - 09/20/2012 : 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
India
47069 Posts |
Posted - 09/20/2012 : 23:16:25
|
just group on MONTH(datefield) for monthly average
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sqlbug
Posting Yak Master
Canada
177 Posts |
Posted - 10/10/2012 : 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 |
Edited by - sqlbug on 10/10/2012 18:48:40 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 10/10/2012 : 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
Canada
177 Posts |
Posted - 10/11/2012 : 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
Canada
177 Posts |
Posted - 10/11/2012 : 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. |
Edited by - sqlbug on 10/11/2012 15:11:31 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 10/11/2012 : 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/
|
|
|
| |
Topic |
|
Averaging Monthly
