Script to Return Last Weeks Data

,

I was asked by a customer to create a scheduled weekly report detailing work that had been completed in the previous week (Monday to Friday) I figured they might lose the report or something might happen to stop the scheduler from running it, and I didn't want to have to modify my script to work out date ranges for the selection criteria if I ran the report later in the current week.  So the script below works with a variable date range based on a calculation of the current weekday. The dates are converted to a varchar(12) to truncate timestamps.

However, this is only good if you run the report on a weekday (Mon-Fri), it kinda  falls over if you run it on a weekend as it will include weekend dates which is easily fixed by filtering the dates to ignore weekdays 1 & 7.  But then, who works weekends?

/*
Return last weeks data from any weekday (2-6) of the current week
*/

select  *
    from [tablename] (nolock)
where	[tablename.date_field] < convert (char(12),(dateadd(day,(0-(select datepart(weekday,getdate()))),getdate()))) and 
	[tablename.date_field] > convert (char(12),(dateadd(day,((0-(select datepart(weekday,getdate())))-5),getdate()))) 

GO

Rate

Share

Share

Rate