a rolling year order by month - 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

SQL Server 2008 Forums

Transact-SQL (2008)

a rolling year order by month

Author

Topic

peo
Starting Member

15 Posts

Posted - 2011-06-07 : 16:51:10

This nasty query fills a dataset to be shown in a line chart.
Grouped by month and order by month. Works ok for a calendar year.
But I would like to work with any 12 months, start month of choice.

Select RIGHT('00' + Cast(DatePart(Month,[SMPTIME]) As varchar(2)),2) As SMPTime,MAX([TT_OUTDOOR_MAX]) As MaxVal,
ROUND(AVG([TT_OUTDOOR_AVG]),2) As AvgVal, MIN([TT_OUTDOOR_MIN]) As MinVal,Round(SUM([TotEnergy]),0) as KWh, 0.0 As placeholder
From [ENERGYSTAT] Where [PAID]='000502'
And [SMPTIME] > '2011-01-01' And [SMPTIME] < '2012-01-01'
Group by DatePart(Month,[SMPTIME]) Order By DatePart(Month,[SMPTIME])

Thanks for suggestions.

/P

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-07 : 17:06:26

[code]DECLARE @startmonth DATETIME
SET @startmonth='2011-01-01'

SELECT CONVERT(CHAR(2),[SMPTIME],1) AS SMPTime,
MAX([TT_OUTDOOR_MAX]) AS MaxVal,
ROUND(AVG([TT_OUTDOOR_AVG]),2) AS AvgVal,
MIN([TT_OUTDOOR_MIN]) AS MinVal,
ROUND(SUM([TotEnergy]),0) AS KWh,
0.0 AS placeholder
FROM [ENERGYSTAT]
WHERE [PAID]='000502'
AND [SMPTIME] > @startmonth AND [SMPTIME] < DATEADD(MONTH,12,@startmonth)
GROUP BY CONVERT(CHAR(2),[SMPTIME],1)
ORDER BY CONVERT(CHAR(2),[SMPTIME],1)[/code]You can make @startmonth a parameter and make the whole thing into a stored procedure.

peo
Starting Member

15 Posts

Posted - 2011-06-07 : 17:14:52

Thanks for your reply.
But this doesn't change the result.
If I set startdate to ex. '2010-11-01', I will still get it ordered by month number, Nov and Dec of 2010 will come after the 2011 months.
I guess I will have to include the year as well, or is there a smarter way?

/P

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-07 : 17:21:03

[code]DECLARE @startmonth DATETIME
SET @startmonth='2011-01-01'

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0,[SMPTIME]),0) AS SMPTime,
MAX([TT_OUTDOOR_MAX]) AS MaxVal,
ROUND(AVG([TT_OUTDOOR_AVG]),2) AS AvgVal,
MIN([TT_OUTDOOR_MIN]) AS MinVal,
ROUND(SUM([TotEnergy]),0) AS KWh,
0.0 AS placeholder
FROM [ENERGYSTAT]
WHERE [PAID]='000502'
AND [SMPTIME] > @startmonth AND [SMPTIME] < DATEADD(MONTH,12,@startmonth)
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0,[SMPTIME]),0)
ORDER BY 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources