February 14, 2013 at 2:07 pm
So I have two t-script setup separately and the emails go out fine. I'm having a hard time combining them into one t-script and two tables.
Basically, this email pull queries done in the last 24 hours and shoot out an email of the results onto a table via email message. Currently, two separate emails go out with two different table. How can I combine them into one email and two tables?
Declare @dict int,
@custDictated nvarchar(max),
@qty int,
@modality varchar(5),
@ttl int,
@msgbody nvarchar(max)
set @ttl = 0
begin
set @custDictated = '<table border="1"><tr> <th ALIGN=left>Modality</th><th>Qty</th></tr>'
Begin
declarecursorName CURSOR for
select
modality,
COUNT(STUDIES.study_id) as qty
from
opalrad.dbo.STUDIES,
opalrad.dbo.MODALITIES
where
STUDIES.modality_id = MODALITIES.modality_id
and opalrad.dbo.STUDIES.study_datetime >= DATEADD(DAY, -7, GETDATE())
and opalrad.dbo.STUDIES.study_recvd_ts >= DATEADD(hour, -24, getdate())
and institution_id = 107
group by
modality
Begin
declarecursorName CURSOR for
select
modality,
COUNT(STUDIES.study_id) as qty
from
opalrad.dbo.STUDIES,
opalrad.dbo.MODALITIES
where
STUDIES.modality_id = MODALITIES.modality_id
and opalrad.dbo.STUDIES.study_datetime >= DATEADD(DAY, -7, GETDATE())
and opalrad.dbo.STUDIES.study_recvd_ts >= DATEADD(hour, -24, getdate())
and institution_id = 111
group by
modality
OPEN cursorName;
FETCH NEXT FROM cursorName
INTO @modality, @qty
While @@FETCH_STATUS = 0
begin
set @custDictated = @custDictated + '<tr><td>' + @modality + '</td><td>'+ cast(@qty as varchar(10)) + '</td></tr>'
set @ttl = @ttl + @qty
FETCH NEXT FROM cursorName
INTO @modality, @qty
end
CLOSE cursorName;
end
set @custDictated = @custDictated + '<tr><td>Total</td><td>'+ cast(@ttl as varchar(10)) + '</td></tr></table>'
set @msgbody = 'The below studies were sent in the last 24 hours by Doshi
This is removing studies that are dated older than 7 days
'+ @custDictated
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TRGEmail',
@recipients = 'test@test.org',
@body_format='HTML',
@body = @msgbody,
@subject = 'test1 Sending Volume';
end
end
Declare @dict int,
@custDictated nvarchar(max),
@qty int,
@modality varchar(5),
@ttl int,
@msgbody nvarchar(max)
set @ttl = 0
begin
set @custDictated = '<table border="1"><tr> <th ALIGN=left>Modality</th><th>Qty</th></tr>'
Begin
declarecursorName CURSOR for
select
modality,
COUNT(STUDIES.study_id) as qty
from
opalrad.dbo.STUDIES,
opalrad.dbo.MODALITIES
where
STUDIES.modality_id = MODALITIES.modality_id
and opalrad.dbo.STUDIES.study_datetime >= DATEADD(DAY, -7, GETDATE())
and opalrad.dbo.STUDIES.study_recvd_ts >= DATEADD(hour, -24, getdate())
and institution_id = 111
group by
modality
OPEN cursorName;
FETCH NEXT FROM cursorName
INTO @modality, @qty
While @@FETCH_STATUS = 0
begin
set @custDictated = @custDictated + '<tr><td>' + @modality + '</td><td>'+ cast(@qty as varchar(10)) + '</td></tr>'
set @ttl = @ttl + @qty
FETCH NEXT FROM cursorName
INTO @modality, @qty
end
CLOSE cursorName;
end
set @custDictated = @custDictated + '<tr><td>Total</td><td>'+ cast(@ttl as varchar(10)) + '</td></tr></table>'
set @msgbody = 'The below studies were sent in the last 24 hours by test3
This is removing studies that are dated older than 7 days
'+ @custDictated
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TRGEmail',
@recipients = 'test@test.org',
@body_format='HTML',
@body = @msgbody,
@subject = 'test3';
end
end
February 14, 2013 at 2:13 pm
There are several things I would change here.
First you are using the old style of join. You should change that to an inner join.
Secondly, and probably the biggest, you don't need a cursor here.
We can certainly help but we need to have some ddl and sample data to work with. Please see the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
February 14, 2013 at 2:15 pm
what do you mean by ddl?
you mean sample results?
February 14, 2013 at 2:32 pm
andyl9063 (2/14/2013)
what do you mean by ddl?you mean sample results?
ddl = data definition language. That means the create table statements. The article I referenced explains how to put all this together in a nice package so the people helping can focus on the issue instead of trying to figure out how to setup the problem.
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply