| Author |
 Topic  |
|
|
belo_nl
Starting Member
6 Posts |
 Posted - 06/18/2012 : 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.
|
Edited by - belo_nl on 06/18/2012 15:55:47
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/18/2012 : 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 - 06/18/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/18/2012 : 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 - 06/18/2012 : 07:51:40
|
Excellent. Works like a charm. :-)
Thank you.
SQL TEAM thumbs up |
|
|
|
belo_nl
Starting Member
6 Posts |
Posted - 06/18/2012 : 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 - 06/18/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/19/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/19/2012 : 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 - 06/19/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/19/2012 : 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. |
|
|
| |
Topic |
|
|
|
calculate the rise in % of avg. temp
