March 6, 2008 at 5:02 am
Hi
I need help to figure out how to code a report for an audit table audit_t where the last field is audit_changed datetime type. It needs to report on rows that fall into the the last seven day range. I am not a programmer so any basic help would be great.
Also should I put the output to xml as I need to set up a weekly dts job to run this script and email to a business user the results ?
Thanks in Advance
March 6, 2008 at 5:41 am
Something like
select
MyColumn1,
MyColumn2,
MyColumn3,
audit_changed
from dbo.audit_t
where audit_changed >= dateadd(dd,-7,getdate())
should do the job. As for emailing it it depends on how you intended to run this query - if it's run from a SQL Agent job you can use a number ways to email it, from SQL Mail to cdosys to a 3rd party component. Not sure you need DTS for this unless you need to transform the results somehow. If you need in XML format you can even coax a well-formed document out of most result sets with plain TSQL.
Regards,
Jacob
March 6, 2008 at 11:26 am
Beware the difference between dateadd and datediff. If you use dateadd(dd,-7,getdate()) you'll get records inserted since this time last week. Unless you run it at EXACTLY the same time every week there'll be possible gaps and duplications.
If you use datediff you can ignore the time element of the date:
where datediff(dd,audit_date,getdate()) between 1 and 8
will give you the last seven complete days' data, whatever time you run it.
As the report covers a seven day period you could use
where datediff (wk,audit_date,getdate())
to get last week's data, whatever day you run it.
March 6, 2008 at 3:48 pm
Good point Stephen - however your solution makes the WHERE clause non-SARGable as you're putting the datediff() function on the audit_changed column, and that's likely to cause the optimizer to ignore any indexes on the column. If you need all rows from the past 7 whole calendar days rather than the last 7 days exactly it would be best to calc the start of the current day and still use dateadd() on the right side of the comparison. Either create a seperate function like so:
create function [dbo].[fn_StartOfDay] (@dteDate DateTime)
returns datetime
as
begin
return convert(datetime, convert(varchar,datepart(m,@dteDate)) + '/' + convert(varchar,datepart(d,@dteDate)) + '/' + convert(varchar,datepart(yyyy,@dteDate)))
end
or put the same code inline into the query - either way then do something like this:
declare @StartOfToday datetime
select @StartOfToday = dbo.fn_StartOfDay(getdate())
select
MyColumn1,
MyColumn2,
MyColumn3,
audit_changed
from dbo.audit_t
where audit_changed >= dateadd(dd,-7,@StartOfToday)
You will then get a consistent 7 calendar days worth of rows no matter what time of day you run it, but can still use any indexes on audit_changed.
Regards,
Jacob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply