Bank Holiday issues

Question

Post reply

Bank Holiday issues

mark lake

Say Hey Kid

Points: 702
More actions
July 5, 2006 at 3:25 am

#168826

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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

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

stax68 SSChampion Points: 11711 More actions · Answer 2

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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

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

mark lake Say Hey Kid Points: 702 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 6

Here's another way to find what the day of the week is without regard to @@DateFirst...

SELECT DATENAME(dw,GETDATE())

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 7

I absolutely agree... for man-power scheduling purposes, ok... for simple reporting, doesn't matter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Yeah, but it's the literal dayname, thus the result is language dependent.

/Kenneth

Phil Factor SSC-Insane Points: 20244 More actions · Answer 9

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