calculate the rise in % of avg. temp

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

calculate the rise in % of avg. temp

Author

Topic

belo_nl
Starting Member

6 Posts

Posted - 2012-06-18 : 05:51:59

Hallo,

I just started with sql and I'm trying to solve this exercise:

1. calculate the average temp per location.
2. show the % increase of the avg. temp per month.

The tables that I made are:

CREATE DATABASE [TEMPERATURE]

CREATE TABLE [TEMP]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[Data] [datetime],
[Location] [NVARCHAR] (MAX),
[Temperature] [int]

)

To calculate avg I'm using this:

SELECT month(Dag) AS [Maand], avg(Temperatuur) AS [Gem.Temperatuur]

FROM TEMP

GROUP BY month(Dag)

ORDER BY month(Dag);

Any thoughts about how to resolve the second part?
Thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-18 : 06:16:18

Assuming yoou just have one year
;with cte as
(
SELECT month(Dag) AS mth, avg(Temperatuur) AS AvTemp
)
select c1.*, PctChange = (c2.AvTemp/c1.AvTemp)*100
from cte c1
left join cte c2
on c2.mth = c1.mth-1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

belo_nl
Starting Member

6 Posts

Posted - 2012-06-18 : 07:06:02

Thx for the replay. Only I'm getting a strange read out: invalid column name....
The dates are from 1.12.2011 till today

CREATE DATABASE [TEMPERATUUR]

CREATE TABLE [TEMP]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[Dag] [datetime],
[Locatie] [NVARCHAR] (MAX),
[Temperatuur] [int]
)

VALUES('12/1/2011','Rotterdam','12');
INSERT INTO [TEMP] ([Dag], [Locatie], [Temperatuur])
......
VALUES('12/31/2011','Rotterdam','12');
INSERT INTO [TEMP] ([Dag], [Locatie], [Temperatuur])
VALUES('1/1/2012','Rotterdam','10');
INSERT INTO [TEMP] ([Dag], [Locatie], [Temperatuur])
.......

VALUES('18/6/2012','Rotterdam','10');
INSERT INTO [TEMP] ([Dag], [Locatie], [Temperatuur])

SELECT month(Dag) AS [Maand], avg(Temperatuur) AS [AvTemp]

FROM TEMP

GROUP BY month(Dag)

ORDER BY month(Dag);

;with TEMP as
(
SELECT month(Dag) AS mth, avg(Temperatuur) AS AvTemp
)

select c1.*, PctChange = (c2.AvTemp/c1.AvTemp)*100
from TEMP c1
left join TEMP c2
on c2.Dag = c1.Dag-1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-18 : 07:35:27

How about

declare @temp table
(
[id] [int] IDENTITY(1,1) NOT NULL,
[Dag] [datetime],
[Locatie] [NVARCHAR] (MAX),
[Temperatuur] [int]
)
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20110912','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20111231','Rotterdam','12');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20121101','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20121018','Rotterdam','10');
insert @temp ([Dag], [Locatie], [Temperatuur])
VALUES('20120818','Rotterdam','5');

SELECT month(Dag) AS [Maand], avg(Temperatuur) AS [AvTemp]
FROM @TEMP
group by month(Dag)
ORDER BY month(Dag);

;with TEMP as
(
SELECT month(Dag) AS [Maand], avg(Temperatuur) AS [AvTemp]
FROM @TEMP
group by month(Dag)
)
select c1.*, PctChange = convert(decimal(8,2),(1.0*(c1.AvTemp-c2.AvTemp)/c2.AvTemp)*100)
from TEMP c1
left join TEMP c2
on c2.Maand = c1.Maand-1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

belo_nl
Starting Member

6 Posts

Posted - 2012-06-18 : 07:51:40

Excellent. Works like a charm. :-)
Thank you.

SQL TEAM thumbs up

belo_nl
Starting Member

6 Posts

Posted - 2012-06-18 : 09:17:49

Just one note. Im not able to use ORDER BY so i can't get my months in a good order.

The PctChange should look like this:
12 - 01 - 02
NULL - % - %

but I'm getting
01 - 02 - 12
NULL - % - %

Im not able to put data from last year(2011)

belo_nl
Starting Member

6 Posts

Posted - 2012-06-18 : 13:30:25

Hi guys i still can't figure out how to get % increase of avg. temperature from 2011/12/ and 2012/01. The code is ordering by months. Is it possible to order by year and months?
Any suggestions?

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 locatie, year(Dag) AS [Jaar], month(Dag) AS [Maand],avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by locatie, year(Dag), month(Dag)
ORDER BY locatie, year(Dag), month(Dag);

;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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-19 : 04:09:48

Did yoou see my first comment

"Assuming yoou just have one year"

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-19 : 04:15:46

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 locatie, year(Dag) AS [Jaar], month(Dag) AS [Maand],avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by locatie, year(Dag), month(Dag)
ORDER BY locatie, year(Dag), month(Dag);

;with TEMP as
(
SELECT DATEADD(mm,datediff(mm,0,Dag),0) AS [Maand], avg(Temperatuur) AS [GemTemp]
FROM @TEMP
group by DATEADD(mm,datediff(mm,0,Dag),0)
)
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 = dateadd(mm,-1,c1.Maand)
order by Maand

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

belo_nl
Starting Member

6 Posts

Posted - 2012-06-19 : 05:27:17

Thx for the replay.

One more question.

How should I incorporate triggers?
I would like to send an email when the % rise is bigger than 10%?

Thx!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-19 : 05:30:04

Probably crreate a sheduled job, run the query and send an email.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources