June 20, 2012 at 10:28 am
I got it Lynn. Should have the solution in just a couple minutes. 😀
_______________________________________________________________
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:33 am
OK so I posted some of your last post for reference. I commented out a number of lines and replaced from there to the end of your code.
--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
declare @Body varchar(max)
select @Body = STUFF(
(
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)) + char(10)
from #Results r1
join #Results r2 on r2.RowNum = r1.RowNum + 1
where r2.[%_stijging] > 10
for XML PATH('')
), 1, 0, '')
select @Body --this is to confirm you now have the multiple line body you are expecting.
--uncomment below when you are ready to send your email.
--EXEC msdb.dbo.sp_send_dbmail @profile_name='SQLServer2008 Database Mail Profile',
-- @recipients='xxx@gmail.com',
-- @subject='AvgTemp%',
-- @body = @Body
drop table #Results
That should just about do it I think.
_______________________________________________________________
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:43 am
Sean Lange (6/20/2012)
I got it Lynn. Should have the solution in just a couple minutes. 😀
That's fine, but I helped with the code so I am just curious what the problem is? Plus, two sets of eyes are usually better than one.
June 20, 2012 at 10:47 am
Lynn Pettis (6/20/2012)
Sean Lange (6/20/2012)
I got it Lynn. Should have the solution in just a couple minutes. 😀That's fine, but I helped with the code so I am just curious what the problem is? Plus, two sets of eyes are usually better than one.
Of course. Certainly didn't mean to imply anything there.
The problem was the OP was getting more than 1 row when setting the @TABLE variable so it was throwing the subquery more than 1 row error.
_______________________________________________________________
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:53 am
Hi guys,
Thx for the replay. The email part works flawlessly. But the month selection is still not functioning.
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 is higher than 10% and % = 150.00
The AvgTemp difference for the months 7 and 8 is higher than 10% and % = 20.00
the 150 % increase is between the months 3 and 4.
the 20 % increase is between the months 5 and 6.
June 20, 2012 at 10:57 am
marginalster (6/20/2012)
Hi guys,Thx for the replay. The email part works flawlessly. But the month selection is still not functioning.
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 is higher than 10% and % = 150.00
The AvgTemp difference for the months 7 and 8 is higher than 10% and % = 20.00
the 150 % increase is between the months 3 and 4.
the 20 % increase is between the months 5 and 6.
I'm missing something, month 3 has average temp of 4, month 4 has average temp of 10. From 3 to 4 (shown on line for month 4) is 150%. What's wrong?
June 20, 2012 at 11:25 am
Hi,
The % calculation for months is good but in the message that is being mailed the calculation is being put as 150% for id 5 and 6 not months 3 and 4 as it should be. Column before months contains id i guess. The id 5 and 6 correspondens with months 3 and 4.
June 20, 2012 at 11:37 am
marginalster (6/19/2012)
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
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), 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.Maand as varchar(2)) + ' and ' + cast(r2.Maand 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.Maand as varchar(2)) + ' and ' + cast(r2.Maand 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 20, 2012 at 1:05 pm
OK here it comes all the work put together.;-)
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('20120601','Rotterdam','13');
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
)
declare @Body varchar(max)
select @Body = STUFF(
(
select 'The AvgTemp difference for the months ' + cast(r1.Maand as varchar(2)) + ' and ' + cast(r2.Maand as varchar(2)) + ' is higher than 10% and % = ' + cast(r2.[%_stijging] as varchar(10)) + char(10)
from #Results r1
join #Results r2 on r2.RowNum = r1.RowNum + 1
where r2.[%_stijging] > 10
for XML PATH('')
), 1, 0, '')
select @Body
EXEC msdb.dbo.sp_send_dbmail @profile_name='SQLServer2008 Database Mail Profile',
@recipients='xxx@gmail.com',
@subject='AvgTemp%',
@body = @Body
drop table #Results
Sorry for sometimes unclear explanations and guys thank you so much!!!!
:-D:-D
June 20, 2012 at 1:07 pm
marginalster (6/20/2012)
OK here it comes all the work put together.;-)Sorry for sometimes unclear explanations and guys thank you so much!!!!
:-D:-D
You are welcome. Glad the between us we were able to get this working for you. 😛
_______________________________________________________________
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 1:09 pm
Glad we were able to help.
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply