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
declare cursorName 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
declare cursorName 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
declare cursorName 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
need to combine two t-script
Posted - 02/14/2013 : 16:10:29
