June 19, 2012 at 3:47 am
Hi,
A bit confused on how to trigger an email if average temperature rise is bigger then 10%.
Any suggestions?
Thx!!!
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 [AvTemp]
FROM @TEMP
group by year(Dag), month(Dag)
)
select c1.Maand, c1.AvTemp, '%_stijging' = convert(decimal(8,2),(1.0*(c1.AvTemp-c2.AvTemp)/c2.AvTemp)*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 19, 2012 at 8:01 am
Excellent job posting ddl and sample data. I don't quite understand what you want to happen. Are you looking for an insert/update on a table that will send an email?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 8:17 am
Hi,
What I would like is that each time when increase in AvTemp is higher then 10% an email notification is send.
In the table below for the months 3 and 4.
month - AvTemp - %
5- 7 - NULL
11- 12- NULL
12 - 12 - 0.00
1- 9 - -25.00
2- 5 - -44.44
3 - 4 - 20.00
4 - 10- 150.00
5 - 10- 0.00
Thank you
June 19, 2012 at 8:28 am
So you would need to create a trigger for insert, update and send an email when you determine it needs to be sent.
It seems you have figured out how to find the % change.
What part(s) do you need help with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 8:41 am
Well I'm confused about insert and update. The data (Temp.) is already inside and its not going to be updated.
So I guess the only thing i would need is, to determine when an email should be send.
Im not quiet sure how to trigger it when AvgTemp gets higher then 10%.
P.S
Apologies about the messy code and table results. Im quite new to this.
Thx for the guide on best practices.
June 19, 2012 at 8:56 am
marginalster (6/19/2012)
Well I'm confused about insert and update. The data (Temp.) is already inside and its not going to be updated.So I guess the only thing i would need is, to determine when an email should be send.
Im not quiet sure how to trigger it when AvgTemp gets higher then 10%.
OK so now I am a bit confused. I guess a trigger is not what you want or need here. 🙂
I am totally confused about this email piece. Is this in a stored proc or something? So you have a table with some data and you want to send an email when this runs if any of the % change is > 10%? Depending on the data you need for this email can something as simple as a where clause get what you need?
with TEMP as
(
SELECT year(Dag) as ThisYear, month(Dag) AS [Maand], avg(Temperatuur) AS [AvTemp]
FROM @TEMP
group by year(Dag), month(Dag)
)
select c1.Maand, c1.AvTemp, '%_stijging' = convert(decimal(8,2),(1.0*(c1.AvTemp-c2.AvTemp)/c2.AvTemp)*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)))
where convert(decimal(8,2),(1.0*(c1.AvTemp-c2.AvTemp)/c2.AvTemp)*100) > 10
order by dateadd(mm, c1.Maand - 1, dateadd(yy, c1.ThisYear - 1900, 0));
P.S
Apologies about the messy code and table results. Im quite new to this.
Thx for the guide on best practices.
Actually your code and table results are very clean and easy to read. Many Many Many KUDOS on posting such readily consumable information. I wish more people posted like that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 9:29 am
So you have a table with some data and you want to send an email when this runs if any of the % change is > 10%?
Thats precise what I would want. The data in the email should be like "The AvgTemp diffrence for the month x and y is higher then 10% and %=z"
June 19, 2012 at 9:43 am
Ok so taking your entire ddl and sample data see if this helps.
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 [AvTemp]
FROM @TEMP
group by year(Dag), month(Dag)
)
select ROW_NUMBER() over(order by dateadd(mm, c1.Maand - 1, dateadd(yy, c1.ThisYear - 1900, 0))) as RowNum, c1.Maand, c1.AvTemp, '%_stijging' = convert(decimal(8,2),(1.0*(c1.AvTemp-c2.AvTemp)/c2.AvTemp)*100)
into #Results
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));
if exists
(
select *
from #Results r1
join #Results r2 on r2.RowNum = r1.RowNum + 1
where r2.[%_stijging] > 10
)
select 'The AvgTemp difference for the months ' + cast(r1.RowNum as varchar(2)) + ' and ' + cast(r2.RowNum as varchar(2)) + ' is higher than 10% and % = ' + cast(r2.[%_stijging] as varchar(10))
from #Results r1
join #Results r2 on r2.RowNum = r1.RowNum + 1
where r2.[%_stijging] > 10
drop table #Results
This will build your string when there is data that meets your criteria. If this is correct, all you need to do is send this as an email?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 10:31 am
I turned on all three tables (AvgTemp, % increase, if > 10 ....) so i get a full report. Everything works perfectly. Thank you!!!!!
Only I'm still not sure how to send an email containing the third result.
June 19, 2012 at 10:35 am
OK so the hard part is done then. 😀 Now you just need to send the email. This is actually fairly easy. You can send email directly from sql server. Take a glance up a bit and check out the link I posted to Database Mail. that link explains what it is and how to configure it. If you already have it enable and configured you can send email using sp_send_dbmail. http://msdn.microsoft.com/en-us/library/ms190307%28v=sql.105%29.aspx
Hopefully this is getting you close?!?!?!?!?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 10:44 am
Thank you again. I'll let you know.
June 19, 2012 at 3:14 pm
Here it is. Finally 😀
Thank you again for all the help.
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), 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));
with TEMP as
(
SELECT year(Dag) as ThisYear, month(Dag) AS [Maand], avg(Temperatuur) AS [AvTemp]
FROM @TEMP
group by year(Dag), month(Dag)
)
select ROW_NUMBER() over(order by dateadd(mm, c1.Maand - 1,
dateadd(yy, c1.ThisYear - 1900, 0))) as RowNum, c1.Maand, c1.AvTemp,
'%_stijging' = convert(decimal(8,2),
(1.0*(c1.AvTemp-c2.AvTemp)/c2.AvTemp)*100)
into #Results
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));
if exists
(
select *
from #Results r1
join #Results r2 on r2.RowNum = r1.RowNum + 1
where r2.[%_stijging] > 10
)
select 'The AvgTemp difference for the months ' + cast(r1.RowNum as varchar(2)) + ' and ' + cast(r2.RowNum as varchar(2)) + ' is higher than 10% and % = ' + cast(r2.[%_stijging] as varchar(10))
from #Results r1
join #Results r2 on r2.RowNum = r1.RowNum + 1
where r2.[%_stijging] > 10
DECLARE @TABLE NVARCHAR(MAX) ;
SET @TABLE =
CAST((
select 'The AvgTemp difference for the months ' + cast(r1.RowNum as varchar(2)) + ' and ' + cast(r2.RowNum as varchar(2)) + ' is higher than 10% and % = ' + cast(r2.[%_stijging] as varchar(10))
from #Results r1
join #Results r2 on r2.RowNum = r1.RowNum + 1
where r2.[%_stijging] > 10 ) AS NVARCHAR(MAX) )
EXEC msdb.dbo.sp_send_dbmail @profile_name='SQLServer2008 Database Mail Profile',
@recipients='xxx@gmail.com',
@subject='AvgTemp%',
@body = @TABLE
drop table #Results
June 19, 2012 at 3:17 pm
Excellent!!! Glad you get it working and thanks for letting me know. Super cool of you to post your solution so others can glean some info from your hard work. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2012 at 10:11 am
Hi,
I just found out that if the avgtemp difference is higher then 10% between more then two months I'm getting an error.The mail is sent empty
and the error message is Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The other thing is that the message The AvgTemp difference for the months.... labels not the months but the column before the months.
id - months - avgtemp - %
1 - 11- 12- NULL
2 - 12- 12- 0.00
3 - 1- 9- -25.00
4 - 2- 5- -44.44
5 - 3- 4- -20.00
6 - 4- 10- 150.00
7 - 5- 10- 0.00
8 - 6- 12- 20.00
The AvgTemp difference for the months 5 and 6..... should be 3 and 4
The AvgTemp difference for the months 7 and 8......should be 5 and 6
Any suggestions?
June 20, 2012 at 10:27 am
Code looks very familiar. I'm just having a slight problem figuring out what the problem is, can you expand a little more on what is wrong?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply