November 27, 2014 at 6:42 am
Hey everyone,
I have this report i setup as an agent job in SSMS; It's setup to run every 7 minutes and it only sends the report if data is present. I'd like to add the ability to omit any rows that were sent in the previous report.
This is what the script looks like:
if exists (select o.ord_billto, o.ord_refnum , o.ord_hdrnumber, o.mov_number, o.ord_status, o.ord_cmdvalue, o.ord_startdate
from orderheader o
where ord_billto in ('A','B','C','D')
and DATEDIFF(minute , o.ord_datetaken, GETDATE())<=7
and ord_cmdvalue >= 250000
and ord_status in ('avl','pln','std','cmp')
)
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(minute , o.ord_datetaken, GETDATE())<=7
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='E-mail@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
Also, on a side note, can anyone explain why i can't seem to use IF ( Select [...]) > 0? When i try using that instead of IF EXISTS i get this error: "Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Any input is appreciated! Thank you!
November 27, 2014 at 8:06 am
I got a tip from my post on stackoverflow for this suggesting to use an extra column on my orderherader table and set a value there as a "flag" of sorts. This lit a light bulb in my head because the report i'm basing my script on had an extra portion of code at the bottom that i didn't bother to look at. Turns out we have 15 extra columns in the Orderheader table that's used for this purpose.
I added this portion of code at the bottom of the script after the SP_Send_dbmail and will update my post when i get some results.
update orderheader set ord_extrainfo2=1
where ord_billto in ('A','B','C','D')
and DATEDIFF(minute , ord_datetaken, GETDATE())<=7
and ord_cmdvalue >= 250000
and ord_status in ('avl','pln','std','cmp')
update orderheader
set ord_extrainfo1=NULL
where ord_billto in ('A','B','C','D')
and DATEDIFF(minute , ord_datetaken, GETDATE())<=7
and ord_cmdvalue <= 250000
and ord_status in ('avl','pln','std','cmp')
and ord_extrainfo2<>NULL
November 27, 2014 at 8:21 am
be careful using datediff(), it only registers the number of 'ticks' between two dates
select DATEDIFF(YEAR,'2013-12-31','2014-01-01') -- one day
select DATEDIFF(YEAR,'2013-01-01','2014-12-31') -- almost 2 years
The first is one day, the second is 729 days but both are only one tick of the year
You are safer using dateadd() and comparing the derived date with the target date.
I would be tempted to turn the query into a stored procedure and set a value in a table for the last time the process was run and then pick up all records since the last process datetime.
November 27, 2014 at 9:24 am
I played around with the Datediff in the script just to test the script to get some results; I don't think this would apply here as we're telling it to refer to the ord_datetaken column which is a date datatype, so we're just telling it to go back 7 minutes as per the job schedule.
Do you have any reading material you can suggest for creating stored procs? I'm still "in training", learning as much as i can from the ground up and i haven't gotten to trying anything with SP.
I understand that once the stored proc is created, i would just have to have an EXEC function in the agent job and call the new SP. I'll see if we have any existing reports that work like this and I'll post an update tomorrow.
Thanks for the input Aaron!
November 27, 2014 at 9:33 am
@ salokbi,
You are NOT going back 7 minutes, you are going back anywhere between 7m0s and 7m59.999s
If you script initiates at 12:07:45 you will get all records from 12:00:00 - that is NOT 7 minutes,
If it next runs at 12:14:59 you will get all the records from 12:07 - records from 12:07:00 to 12:07:45 will appear in both reports
if it next runs as 12:15:01 it will get all records from 12:08:00. any records that appeared between 12:07:46 and 12:07:59 will not be picked up.
2 seconds difference in the execution start makes a hole minutes difference to the records selected. [technically 1 millisecond is all that is needed - 12:14:59.999 to 12:15:00.0000]
if it is a date datatype then how can it go back 7 minutes - there is no time element?
Generally to create a stored procedure, write the code you would normally write in your query then wrap the query in
CREATE PROCEDURE <ProcedureName> AS
BEGIN
<your code goes here...>
END
and execute the query; this will create the stored procedure. You can then run the stored procedure using
EXEC <ProcedureName>
There is loads more to it if you need to pass in parameters or run the proc under specific user priveleges but this is the basics.
The stored procedure can execute a number of separate SQL statements as a single process. e.g. define a variable, get the value from the database, run a query to return a result set and update the records to mark them as output.
you can also return multiple results sets from a stored procedure, but you need to take care if these are being consumed downstream by a .NET component or similar (SSIS task, Access procedure etc) as you need to define which result set will be used.
November 27, 2014 at 10:11 am
Ok now i see! Thank yo very much for the breakdown!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply