July 5, 2006 at 3:25 am
A lot of our sps run with the following code at the start...
SET @start_date = (select MAX(StartDate) As Date2 from _week_start where StartDate <= getdate()-1)
SET @end_date =getdate()
They run every night so people can keep a running total of the week so far.
The _week_start table is a simple table of one column that shows the first Monday of every week...
2006-06-05 00:00:00.000 2006
2006-06-12 00:00:00.000 2006
2006-06-19 00:00:00.000 2006
2006-06-26 00:00:00.000 2006
2006-07-03 00:00:00.000 2006
2006-07-10 00:00:00.000 2006
2006-07-17 00:00:00.000 2006
2006-07-24 00:00:00.000 2006
2006-07-31 00:00:00.000 2006
2006-08-07 00:00:00.000 2006
The problem comes on Bank Holiday Monday's... or rather the Tuesday after. Since @start_date then gets populated with this weeks date rather than last weeks (obviously the Monday report should show a whole 7 days, thus being the most important day for the report).
I've found a solution by using a "bank holiday tuesday" table that then adjusts the @start_date to -2 from getdate() thus returning last week.
However I was hoping on something a little whizzier... could anyone help?
July 5, 2006 at 3:45 am
Perhaps you could benefit from a table with some more stuff in it than just each monday's date.
Have a look here for a description of a more complete dates-table.
http://www.aspfaq.com/show.asp?id=2519
/Kenneth
July 5, 2006 at 11:33 am
It sounds like you are overwriting the data every night. You could retain data from the previous Monday, so people can query 'previous week' as well as 'week to date'.
(might they not want to see yesterday's data even if it was a holiday? Is it impossible for the data to change on a bank holiday? What if the person that needs to see the data is off sick one Monday, so can't view the 'previous week' data?)
That way it's clear to everyone exactly what data they are looking at, and the move can be presented as a proactive reporting strategy - today 'previous week', tomorrow Teradata!!!
Of course maybe I have misunderstood your reporting system...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 5, 2006 at 12:29 pm
Forgive me for presuming to re-write your routine, but I don't see why you need the table at all. If all you are doing is getting the datetime value of the previous monday then something like this would do the trick: No need to have a table of 'mondays'. It would need to be checked, of course but I think the routine below will find the previous monday whatever the setting on your server for the 'start of the week'.
I have done more than my fair share of SQL Reports over the years but I'm too foolish to understand why bank holidays would affect the reporting, but you can always alter the routine to give you the previous tuesday if there has been a bank holiday on the monday.
the routine is in a test harness that is currently set for today's date. Try it and see if it checks out!
DECLARE @TheDate DateTime SELECT @TheDate=GETDATE() SELECT DATEADD(DAY,-(((DATEPART(dw,@TheDate)+@@DateFirst-3) % 7)+1),@TheDate)
Best wishes,
Phil Factor
July 6, 2006 at 2:05 am
Here's how to find out which particular weekday any given date is, regardless of what @@DATEFIRST is set to.
--==== return weekday of @myDate regardless of @@DATEFIRST settings
--==== 1 = Monday
--==== 2 = Tuesday
--==== 3 = Wednesday
--==== 4 = Thursday
--==== 5 = Friday
--==== 6 = Saturday
--==== 7 = Sunday
declare @myDate char(8)
set @myDate = convert(char(8), getdate(), 112)
select ( @@datefirst + datepart(weekday, @mydate) -2 ) %7 + 1
go
/Kenneth
July 6, 2006 at 3:58 am
Thanks for this... it's just what was needed and is now being used in other places as well.
As for why bank holidays make such a difference? Well because no one is here on a Monday, and these reports run automatically overnight... we want to minimise the amount of manual work needed.
Also true we could write each report to an individual date... but the sheer number would fill the disk up in no time.
So thanks for everyones advice and help, but method 1 is the way for me.
And as usual I've credited where credit is due. As I believe everyone should.
July 6, 2006 at 6:37 am
Here's another way to find what the day of the week is without regard to @@DateFirst...
SELECT DATENAME(dw,GETDATE())
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2006 at 6:39 am
I absolutely agree... for man-power scheduling purposes, ok... for simple reporting, doesn't matter.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2006 at 8:51 am
Yeah, but it's the literal dayname, thus the result is language dependent.
/Kenneth
July 6, 2006 at 2:36 pm
Surely Mark wants to get a DateTime value into @Start_Date which normally corresponds to the last monday, but to the monday before that if there has been a bank holiday.
I can't see why he would benefit from knowing it was 'Thursday' today, or having integer value from 1 to 7.
My routine was designed to give him the DateTime corresponding to monday, whatever the setting of @@DateFirst. (some other routine on the SPID could change it or rely on it being set to the default value) He can then just do the DateAdd (week,-1,@Start_date) if there has been a bank holiday.
Dead simple
Of course I can say I wouldn't do the reporting quite that way but then I havn't got his problem and I don't know his constraints or circumstances. So Instead I say.. I hope that helps, Mark and good luck!
Best wishes,
Phil Factor
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply