Holiday Dates 2

[sanctified]

Hi Group,
Currently, I have a Stored Procedure which is called from my ASP Page. It takes into account Fridays and weekends passing the value in as a parameter:

snippet....
if @pListType = 'F' -- being a Friday
begin
SELECT su.SuspensionID, su.ConsentNo, si.SiteID,
si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc,
si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached, rd.RefValue AS NoticeDays, si.MergeID
FROM Suspension su
INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN RefData rd ON si.NotReq = rd.RefCode
WHERE su.Status = 'ACT'
and si.Archived = 'N'
and (datediff(day, getdate(), si.SuspensionStart) = rd.RefValue
or datediff(day, getdate(), si.SuspensionStart) = rd.RefValue + 1
or datediff(day, getdate(), si.SuspensionStart) = rd.RefValue + 2)
end

The above is saying where the day is a Friday and the difference between the current date and the suspension date = the notice period (rd.refvalue) OR the difference between the current date and the suspension date = the notice period plus 1 or 2 days (to take into account Saturday and Sunday)print the signs. I need to incorporate holiday dates into this routine.
For the UK Bank Holiday which just passed (29th May), I fudged it by using the following code:

If substring(cast(getdate() as varchar),1,11) = 'May 26 2006'

begin
SELECT su.SuspensionID, su.ConsentNo, si.SiteID,
si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc,
si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached,
rd.RefValue AS NoticeDays, si.MergeID
FROM Suspension su
INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN RefData rd ON si.NotReq = rd.RefCode
WHERE su.Status = 'ACT'
and si.Archived = 'N'
and (datediff(day, getdate(), si.SuspensionStart) = rd.RefValue
or (si.SuspensionStart = '05/06/2006'
end

these needed to go out on Friday 26th May...

Firstly I will need to take into account the day of the week for the holiday. I have created a table called tblHolidayDates with a field called Holiday_Date.
I'm a bit confused on how to implement it/call my table from within the SP. Once I have this, I will know how to code the remainder.

Any pointers much appreciated.

 

[sanctified]

George,
To make things easier, we can ignore the weekends. The ASP page which calls the stored procedure caters for this as a passed in parameter.
The holiday calculation will come at the top of the SP and any records matching the criteria will be pulled as well as those where the posting will occur on a weekend.

I'm only interested in the holiday calculation.

cheers

 

[chrissie1]

I think george deserves a star for all his work? Don't you?

Christiaan Baes
Belgium

"My new site" - Me

 

[SQLDenis]

yes a star with at least 10 points

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[sanctified]

Yes, he deserves more than a * and I was going to give him one.

 

[gmmastros]

I ran your code and for a suspension date of 11/07/2006, it gave me 30/06/2006 as the datetopost which is a date in the calendar table.

Which query did you run?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chrissie1]

And know George has to convince you that your logic is all wrong and that you need to see what he is doing and not what you think he is doing.

Christiaan Baes
Belgium

"My new site" - Me

 

[sanctified]

George,
The 2nd one.
George do you want to contact me personally? I don't like the snide comments. I'm new to this forum and I thought people were here to help. I'm quite willing to pay you even though this forum is supposed to be a free. Post your email address if you want me to contact you.

I will look somewhere else for the solution Christian if you are going to monitor my postings!!

Thanks George. You have been very helpful!!

 

[gmmastros]

sanctified,

This forum is free. Whenever I post a response, I don't expect any payment. I've been helping people in this forum for almost a year and a half and have never received a single penny. I help others because there was a time when I needed help, and received it. It is better to keep all communication within the forum because the help given in a post may also be helpful for others.

I think the point that Christiaan was trying to get across is that you can trust that the advice I give is, in fact, good advice. I've made some mistakes in the advice I've given others, but when that happens, others will jump in and correct me. That hasn't happened here yet.

My suggestion to you is this...

1. Create a calendar table that has every date in it. This won't be a big table, so database size and performance will not be affected.

2. Add a bit field column to the table to represent days that you can post (or not). Set every Saturday, Sunday, and holiday in this table to represent days that you cannot post a notice.

Imagine you had a table like this...
[tt][blue]
Date Posting Allowed Weekday
---------- --------------- ---------
01/07/2006 0 Saturday
02/07/2006 0 Sunday
03/07/2006 1 Monday
04/07/2006 0 Tuesday
05/07/2006 1 Wednesday
06/07/2006 1 Thursday
07/07/2006 1 Friday
08/07/2006 0 Saturday
09/07/2006 0 Sunday
10/07/2006 1 Monday
[/blue][/tt]

With this type of table, it would be easy to get the day to post. For example, if the day to post is 09/07/2006, then you could write a query like this...

[tt]
Select Max(CalendarDate) As DayToPost
From Calendar
Where CalendarDate <= '2006-07-09'
And PostingAllowed = 1
[/tt]
This would result in 2006-07-07. Obviously, this date exists in the calendar table, but the PostingAllowed is 1, so we can safely return this date and it becomes a 'good' date to post a notice.

This is, in fact, what my previous code does. When I run the query, I also get 30/06/2006 as a good day to post, but if you notice, PostingAllowed = 1 for this record in the calendar table.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sanctified]

FYI THE SOLUTION... and this was after 2 replies on another forum

and exists (select * from tblHolidayDates where datediff(day, holidaydate, si.SuspensionStart)= rd.RefValue)

This is what I needed. I just had the syntax wrong and it works with more than ONE record in tblHolidayDates.

Thanks for your efforts.

 

[gmmastros]

I'm glad you found a solution.

I'm sorry that I was unable to help you and for wasting your time. It won't happen again!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sanctified]

Not at all. I don't look at it as you wasting my time. Your help was much appreciated.

cheers mate!!