June 18, 2012 at 1:00 pm
Hi guys. I can't get the code to sort the months in the right order.
The code works only if I use only data from the same year.
Any suggestions on how i can incorporate the data for example 2011.12 till 2012.06 and that the avg. temp difference between the months 2011.12 and 2012.01 would also get calculated?
declare @temp table
(
[id] [int] IDENTITY(1,1) NOT NULL,
[Dag] [datetime],
[Locatie] [NVARCHAR] (MAX),
[Temperatuur] [int]
)
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20111112','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20111231','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120118','Rotterdam','9');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120213','Rotterdam','5');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120214','Rotterdam','7');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120215','Rotterdam','4');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120316','Rotterdam','4');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120418','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120501','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20110501','Rotterdam','7');
SELECT month(Dag) AS [Maand], year(Dag) AS [Jaar], locatie, avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by year(Dag), month(Dag), locatie
ORDER BY year(Dag) desc, month(Dag), locatie ;
;with TEMP as
(
SELECT month(Dag) AS [Maand], avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by month(Dag)
)
select c1.*, '%_stijging' = convert(decimal(8,2),(1.0*(c1.GemTemp-c2.GemTemp)/c2.GemTemp)*100)
from TEMP c1
left join TEMP c2
on c2.Maand = c1.Maand-1
June 18, 2012 at 2:08 pm
Can you explain what is not sorting in the right order?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 18, 2012 at 2:24 pm
the results that I'm getting when the % diffrence is being calculated is in this order:
01-2012 = NULL
02-2012 = %
12-2011 = NULL
what i would like to have is
12-2011 = NULL
01-2012 = %
02-2012 = %
I thought i could resolve it with adding 'order by' but then i got the following message: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
;with TEMP as
(
SELECT month(Dag) AS [Maand], avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by month(Dag)
order by month(Dag)
)
select c1.*, '%_stijging' = convert(decimal(4,0),(1.0*(c1.GemTemp-c2.GemTemp)/c2.GemTemp)*100)
from TEMP c1
left join TEMP c2
on c2.Maand = c1.Maand-1
June 18, 2012 at 2:33 pm
How about this?
declare @temp table
(
[id] [int] IDENTITY(1,1) NOT NULL,
[Dag] [datetime],
[Locatie] [NVARCHAR] (MAX),
[Temperatuur] [int]
)
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20111112','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20111231','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120118','Rotterdam','9');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120213','Rotterdam','5');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120214','Rotterdam','7');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120215','Rotterdam','4');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120316','Rotterdam','4');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120418','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120501','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20110501','Rotterdam','7');
SELECT month(Dag) AS [Maand], year(Dag) AS [Jaar], locatie, avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by year(Dag), month(Dag), locatie
ORDER BY year(Dag) desc, month(Dag), locatie;
with TEMP as
(
SELECT year(Dag) as ThisYear, month(Dag) AS [Maand], avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by year(Dag), month(Dag)
)
select c1.Maand, c1.GemTemp, '%_stijging' = convert(decimal(8,2),(1.0*(c1.GemTemp-c2.GemTemp)/c2.GemTemp)*100)
from TEMP c1
left join TEMP c2
on c2.Maand = c1.Maand-1
order by dateadd(mm, c1.Maand - 1, dateadd(yy, c1.ThisYear - 1900, 0));
June 18, 2012 at 2:45 pm
Much better thank you. Only the code is still not recognizing the diffreant years.
The avg.temp % difference between 1 and 12 shouldnt be NULL and the 5th month of 2011 should read NULL because there is not a 4th month to compare with. Any idea? 🙁
month - temp - %
5- 7 - -30.00
11 - 12-NULL
12- 12-0.00
1- 9-NULL
2- 5--44.44
3- 4--20.00
4- 10-150.00
5 - 10-0.00
June 18, 2012 at 2:50 pm
marginalster (6/18/2012)
Much better thank you. Only the code is still not recognizing the diffreant years.The avg.temp % difference between 1 and 12 shouldnt be NULL and the 5th month of 2011 should read NULL because there is not a 4th month to compare with. Any idea? 🙁
month - temp - %
5- 7 - -30.00
11 - 12-NULL
12- 12-0.00
1- 9-NULL
2- 5--44.44
3- 4--20.00
4- 10-150.00
5 - 10-0.00
Yep, you are only joining on the month, try this:
declare @temp table
(
[id] [int] IDENTITY(1,1) NOT NULL,
[Dag] [datetime],
[Locatie] [NVARCHAR] (MAX),
[Temperatuur] [int]
)
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20111112','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20111231','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120118','Rotterdam','9');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120213','Rotterdam','5');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120214','Rotterdam','7');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120215','Rotterdam','4');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120316','Rotterdam','4');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120418','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120501','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20110501','Rotterdam','7');
SELECT month(Dag) AS [Maand], year(Dag) AS [Jaar], locatie, avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by year(Dag), month(Dag), locatie
ORDER BY year(Dag) desc, month(Dag), locatie;
with TEMP as
(
SELECT year(Dag) as ThisYear, month(Dag) AS [Maand], avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by year(Dag), month(Dag)
)
select c1.Maand, c1.GemTemp, '%_stijging' = convert(decimal(8,2),(1.0*(c1.GemTemp-c2.GemTemp)/c2.GemTemp)*100)
from TEMP c1
left join TEMP c2
on dateadd(mm, c2.Maand - 1, dateadd(yy, c2.ThisYear - 1900, 0)) = dateadd(mm, -1, dateadd(mm, c1.Maand - 1, dateadd(yy, c1.ThisYear - 1900, 0)))
order by dateadd(mm, c1.Maand - 1, dateadd(yy, c1.ThisYear - 1900, 0));
June 18, 2012 at 2:54 pm
It works like never before 😉
Thank you again!!!
June 18, 2012 at 7:08 pm
Using the original data for testing...
The problem that most folks have with such things is that they forget to change the dates to a [font="Arial Black"]common month date [/font]for each month. The following code does convert each date to a common month date and that allows small miracles to happen. In fact, after doing just that, the original code was remarkably close to being right. (Please change my English column names to suite you).
WITH
cteCalcAvg AS
(
SELECT Locatie,
FirstOfMonth = DATEADD(mm,DATEDIFF(mm,0,Dag),0),
GemTemp = AVG(Temperatuur+0.0)
FROM @Temp
GROUP BY Locatie,DATEDIFF(mm,0,Dag)
)
SELECT c1.Locatie,
MonthYear = SUBSTRING(CONVERT(VARCHAR(30),c1.FirstOfMonth,106),4,30),
c1.GemTemp,
[%_stijging] = CONVERT(DECIMAL(8,2),(c1.GemTemp-c2.GemTemp)/c2.GemTemp*100)
FROM cteCalcAvg c1
LEFT JOIN cteCalcAvg c2
ON c2.FirstOfMonth = DATEADD(mm,-1,c1.FirstOfMonth)
ORDER BY c1.FirstOfMonth
;
There's a built in performance problem with that, though. We're calling a CTE twice and, much like any view would, it must be calculated twice. You can sometimes get quite the performance improvement by dumping the first query into a Temp Table instead of using a 2-call CTE especially when using SELECT/INTO to create the Temp Table. Just don't ever use it across linked servers because it will lock up the source if you do.
The following code produces the same result as the code above but uses a Temp Table instead of a 2-Call CTE. You can test to see which is faster in your particular instance.
SELECT Locatie,
FirstOfMonth = DATEADD(mm,DATEDIFF(mm,0,Dag),0),
GemTemp = AVG(Temperatuur+0.0)
INTO #MyHead
FROM @Temp
GROUP BY Locatie,DATEDIFF(mm,0,Dag)
SELECT c1.Locatie,
MonthYear = SUBSTRING(CONVERT(VARCHAR(30),c1.FirstOfMonth,106),4,30),
c1.GemTemp,
[%_stijging] = CONVERT(DECIMAL(8,2),(c1.GemTemp-c2.GemTemp)/c2.GemTemp*100)
FROM #MyHead c1
LEFT JOIN #MyHead c2
ON c2.FirstOfMonth = DATEADD(mm,-1,c1.FirstOfMonth)
ORDER BY c1.FirstOfMonth
;
I also question the wisdom of having a MAX datatype for Locatie. MAXs and joins don't like each other. Just joining to a MAX will cause the code to run twice as slow as if you used a normal NVARCHAR. Using a MAX datatype will also make it impossible for you to rebuild indexes in an ONLINE fashion as well as a couple of other problems that I can't put my fingers on right now. Pick a more reasonable size and use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2012 at 9:42 pm
Thank you Jeff.
When I'm entering data from different locations the code is sorting and calculating CalcAvg by month and not by location.
example If I enter only one month worth data from a different location I'm not getting NULL for an answer but difference between two months from two different locations. even with ORDER BY c1.Location
Delft Jan 20122.000000-76
RotterdamSep 201112.000000NULL
RotterdamDec 20118.354838NULL
RotterdamJan 20127.189655-14
RotterdamAug 20125.000000NULL
RotterdamSep 2012-1.750000-135
RotterdamOct 201210.000000-671
RotterdamNov 201210.0000000
Is there a quick fix for that? 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply