need to combine two t-script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need to combine two t-script

Author

Topic

andyl9063
Starting Member

19 Posts

Posted - 2013-02-14 : 16:10:29

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 23:08:03

can you tell whats the difference between those SELECT statements? they all look same to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andyl9063
Starting Member

19 Posts

Posted - 2013-02-15 : 10:28:41

yeah they're both the same except they're specifying different institution id.
i just want to combine them into one same email.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 14:15:13

why not put the in same query by using something like


select 
modality,
COUNT(CASE WHEN institution_id  = 107 THEN STUDIES.study_id END) as qty107,
COUNT(CASE WHEN institution_id  = 111 THEN STUDIES.study_id END) as qty111
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  IN (107,111)
group by
modality

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andyl9063
Starting Member

19 Posts

Posted - 2013-02-18 : 09:36:12

that works but I want the email to include the two tables. That only show one table with all the data.
thanks for the hint though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 09:51:03

use UNION ALL to combine both queries before you call sp_send_dbmail statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andyl9063
Starting Member

19 Posts

Posted - 2013-02-18 : 10:23:27

how and where would you enter that? Sorry I'm kinda new to sql queries.
Like below?

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

UNION ALL

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources