SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need to combine two t-script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andyl9063
Starting Member

USA
19 Posts

Posted - 02/14/2013 :  16:10:29  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/14/2013 :  23:08:03  Show Profile  Reply with Quote
can you tell whats the difference between those SELECT statements? they all look same to me

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

Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 02/15/2013 :  10:28:41  Show Profile  Reply with Quote
yeah they're both the same except they're specifying different institution id.
i just want to combine them into one same email.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/15/2013 :  14:15:13  Show Profile  Reply with Quote
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/

Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 02/18/2013 :  09:36:12  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  09:51:03  Show Profile  Reply with Quote
use UNION ALL to combine both queries before you call sp_send_dbmail statement

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

Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 02/18/2013 :  10:23:27  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000