Agent Job reort omit previously sent results

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

SQL Server 2008 Forums

Transact-SQL (2008)

Agent Job reort omit previously sent results

Author

Topic

samalkobi
Starting Member

2 Posts

Posted - 2014-12-04 : 09:02:59

Hey all! My first post on this forum here;
I have this report i'm trying to set up so that any results that were already sent by e-mail in the report to be excluded the next time it runs.

MY code looks like this at the moment and i'm using an additional column on my table where i can set a "flag" as per a suggestion i got on stackoverflow.

if (select count(o.ord_hdrnumber)
from orderheader o
where ord_billto in ('A','B','C','D')
and DATEDIFF(hour  , o.ord_startdate, GETDATE())<=24
and ord_cmdvalue >= 250000
and ord_status in ('avl','pln','std','cmp')
)>0
begin
	declare @tableHTML NVARCHAR(MAX);
	
	SET @tableHTML=
	N'<h1>Test Report</h1>'+
	N'<table border=1>'+
	N'<tr><th>Bill To</th>'+
	N'<th>Reference number</th>'+
	N'<th>Order #</th>'+
	N'<th>Move #</th>'+
	N'<th>Order Status</th>'+
	N'<th>Value</th>'+
	N'<th>Ship date</th>'+
	N'<th>Delivery Date</th>'+
	cast(( select td=o.ord_billto, '',
	td=o.ord_refnum, '',
	td=o.ord_hdrnumber, '',
	td=o.mov_number, '',
	td=o.ord_status, '',
	td=convert(decimal(10, 2), o.ord_cmdvalue), '',
	td=convert(varchar, o.ord_startdate, 101), '',
	td=convert(varchar, o.ord_completiondate, 101), ''
	from orderheader o
where ord_billto in ('A','B','C','D')
and DATEDIFF(hour , o.ord_startdate, GETDATE())<=24
and ord_cmdvalue >= 250000
and ord_status in ('avl','pln','std','cmp')
	for XML path('tr'), type
	) as NVARCHAR(MAX) ) +
	N'</table>' ;
	
	execute msdb.dbo.sp_send_dbmail
	@recipients='email@domain.com',
	@subject='Test Report',
	@body=@tableHTML,
	@body_format='HTML',
	@attach_query_result_as_file='false',
	@query_attachment_filename='EDI.csv',
	@query_result_separator='',
	@query_result_no_padding='FALSE'
end

update orderheader 
set ord_extrainfo2='1'
where ord_billto in ('A','B','C','D')
and DATEDIFF(hour  , ord_startdate, GETDATE())<=24
and ord_cmdvalue >= 250000
and ord_status in ('avl','pln','std','cmp')
and ord_extrainfo2='null'


update orderheader  set ord_extrainfo2='null'
where ord_billto in ('A','B','C','D')
and DATEDIFF(hour  , ord_startdate, GETDATE())<=24
and ord_cmdvalue >= 250000
and ord_status in ('avl','pln','std','cmp')
and ord_extrainfo2='1'

The part at the bottom with the update doesn't seem to be working properly as it's trying to update more rows than the query itself is pulling. Any suggestions of another way of doing this?

1 of the reports we have already setup that does this has the update part of the script written like this, but i can't seem to see where my issue is:

update orderheader
set ord_extrainfo1=1
from orderheader
left join carrier r on r.car_id=ord_carrier
where ord_cmdvalue>r.car_ins_cargolimits
and DATEDIFF(day, ord_startdate, GETDATE())<30
and ord_status in ('CMP','STD','PLN','AVL')
and ord_carrier <> 'UNKNOWN'
and ord_extrainfo1 is null

update orderheader
set ord_extrainfo1=NULL
from orderheader
left join carrier r on r.car_id=ord_carrier
where (ord_cmdvalue<=r.car_ins_cargolimits or ord_carrier = 'UNKNOWN')
and DATEDIFF(day, ord_startdate, GETDATE())<30
and ord_status in ('CMP','STD','PLN','AVL')
and ord_extrainfo1=1

Also, if someone can help me figure out how to get the DATDIFF function to pull the results properly; In the second example i gave with the UPDATE ORDERHEADER, they set it up to go back 30 days based on the order startdate, but when the report runs it only sends the most recent orders entered and anything before that is excluded...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources