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.

 

[gmmastros]

First, take a look at this frequently asked question faq183-5075. There is a lot of valuable information in there.

Second, I would suggest you create a table in the database for ALL calendar dates. Extend it so that in your life time, you'll never have to worry about. The table won't be that large. Add a Column for 'DatesToIgnore'. Then you can inner join on that table and filter on the DatesToIgnore field.

After reading the FAQ and looking over my suggestions, you still have questions, post back here.

-George

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

 

[sanctified]

Thanks George,
I did take a look at that posting, however it is a bit overboard for what I'm doing. I need to know how to 'take into account' the holiday dates (from a table)in my SP calculation that's all.

Maybe later when I'm working on a larger system, I'll use your suggestion.

Regards

 

[gmmastros]

I agree that it seems a bit much. It only took me about 20 minutes to implement it, so it's not really that bad.

Anyway, can you post some sample data with expected results? It doesn't have to be 'real' data. It will help me understand what you are trying to accomplish so that I can help you better.

-George

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

 

[sanctified]

Sample data....
As I said, I fudged this through for the last Bank Holiday 29th May. Running the routine normally on Friday 26th May would have excluded the 5th June records (with a 7 day notice period - these would have been printed on the 29th) but because of the Bank Holiday I've included the 5th June as part of the where clause for the current date 26th May 2006 i.e the week date before the bank holiday.

What I need is how to call the holidaydates table and how to work out what day of the week the holiday lands on. The rest I should be able to work out using the existing 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

 

[gmmastros]

Assuming you have a HolidayDates table with a HolidayDate column, you can determine whether any given date is a holiday with this code...

Declare @IsHoliday Bit
Declare @DateToTest DateTime

Set @DateToTest = [!]'2006-12-25'[/!]

If Exists(Select * From HolidayDates Where HolidayDate = @DateToTest)
	Set @IsHoliday = 1
Else
	Set @IsHoliday = 0

Select @IsHoliday, DateName(Weekday, @DateToTest)

Try running this code in Query Analyzer, changing the @DateToDate value. Also notice the DateName function. You can use it to determine the day of the week that a particular date falls on.

For example:

Select DateName(Weekday, GetDate()) As TodaysWeekdayName,
       DateName(Weekday, GetDate() + 1) As Tomorrow

I'm not really sure if any of this is helpful, but I hope it is.

-George

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

 

[sanctified]

George
This looks good. I'm sure I can make use of it. One thing I need to do first.

I need to check that a holiday is about to happen i.e its no point the code running ON the day of the holiday as the event has already happened. For example, manually I knew the 29th May 2006 was a bank holiday so I fudged the code on the 26th May 2006. I will probably need to use the datediff function to compare the current date and the dates in the holiday table. I think the follwing may work: If the holiday date is different by 1 day OR (3 days AND the holiday date is a Monday - for Friday processing) then do the necessary calculation.

Does this make sense?

Thanks

 

[sanctified]

George,
Thanks for your help. Just to clarify I don't need to determine whether a date is a holiday or not. The dates in this table will be holiday dates. I just need to take these dates into consideration when I'm working out when to print the signs.

Regards

 

[sanctified]

Will this work...
Select * from HolidayDates hd
If datediff(day, getdate(), hd.Holiday_Date) = 1 then
.. work out routine for holiday for weekday prior to holiday processing

else
if datediff(day,getdate(),hd.Holiday_Date) = 3 AND DatePart(dw,hd.Holiday_Date) = 2 then
.. work out routine for holiday which starts on Monday for weekday prior to holiday processing

How does the looping work?

 

[gmmastros]

There are lots of things you can do, but I'm still not sure how it would fit within your query. Sorry.

You can count the number of holidays within a certain date range. You could even exclude holidays that fall on any given day.

Declare @HolidayCount Integer

Select @HolidayCount = Count(1)
From   HolidayDates
Where  HolidayDate Between @StartDate And @EndDate
       And DateName(Weekday, HolidayDate) Not In('Saturday','Sunday')

I hope this helps.



-George

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

 

[sanctified]

Hi George,
Thanks for your help.

The problem:
I have a table of Holiday Dates - not generic ones. The user will input the dates as required.
My application prints out signs x amount of days before the suspension is due to start. The x amount of days can be one of 7, 14 or 28 notice periods and is held in the refvalue field.

Currently my sp uses this formula:
and (datediff(day, getdate(), si.SuspensionStart) = rd.RefValue
so it will pick up all the records today where the difference between the suspension start date and todays date = the notice period. So far so good?

I now need to take into account Holiday Dates, hence the need for a table.
So if a sign was to be printed on Monday but Monday was a Bank Holiday it should print it on the Friday before.

I've played around with it and managed to get it working using this test. I did getdate()-10 as this date was the 26/05/2006 and the Monday 29th/05/2006 was a Bank Holiday. Putting 29/05/2006 in the table and running this yields the correct results. However when more than one record is in the Holiday table I get the following error:
subquery returned more than 1 row error.

Any suggestions?

I hope the problem is much clearer now.
if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))= 3 AND
DATEPART(dw,(select holidaydate from tblHolidayDates)) = 1 -- Monday Date
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()-10 , si.SuspensionStart) = rd.RefValue
OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2)
or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1)
end

 

[gmmastros]

Maybe something like this...

Declare @HolidayCount Integer

Select @HolidayCount = Count(1)
From   HolidayDates
Where  HolidayDate Between @StartDate And @EndDate
       And DateName(Weekday, HolidayDate) Not In('Saturday','Sunday')

if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))= 3  AND
DATEPART(dw,(select holidaydate from tblHolidayDates)) = 1 -- Monday Date 
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()-10 [!]- @HolidayCount[/!] , si.SuspensionStart) = rd.RefValue
    OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2)
        or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1)
end

Under normal circumstances, @HolidayCount will be 0, the essentially, your query will be unchanged. If there are holiday(s) within the time frame, @HolidayCount will reflect how many holidays there are and hopefully adjust the query accordingly.

-George

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

 

[sanctified]

The problem lies with these lines:

OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2)
or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1)

It's the select holidaydate from tblHolidaydates that the subquery returns more than one value is erroring out on. If I delete teh records in the table and leave ONE record it works.

 

[sanctified]

Also, I do not have start and enddate parameters. I am NOT checking holiday dates BETWEEN. I'm just checking for the existance of a day being an holiday as defined in the tblHoliday table which I've done but the query seems to want to return more than ONE value even though the field bewing returned is keyed so should only ever return one record.

Thanks

 

[gmmastros]

Try changing: Select holidaydate from tblHolidayDates

To

Select Min(HolidayDate) From tblHolidayDates Where HolidayDate Between [!]SomeDateThatRepresentsStartDate[/!] And [!]SomeDateThatRepresentsEndDate[/!]

-George

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

 

[gmmastros]

OK. Let's examine this a little closer. You have:

datediff(day, [blue](Select holidaydate from tblHolidayDates)[/blue], si.SuspensionStart)= rd.RefValue + 2

The part I highlighted in [blue]blue[/blue] is the problem.

This part MUST return a single record, with a single field (a scalar value), because you are using this value in a calculation (Date Diff Function). If multiple records are returned, DateDiff will choke.

Since I still don't understand what your query is doing, it is still dificult for me to give you good advice.

You could try changing it to (Select HolidayDate From tblHolidayDates Where HolidayDate = @SomeDate) (that's assuming you have a variable with the date you are checking for.

-George

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

 

[gmmastros]

Let me see if I understand...

You have to figure out when a sign will be posted. The RefValue represents the number of days prior to the SuspensionDate that the sign should be posted on. If SuspensionDate - RefValue falls on a Saturday or Sunday, you want the sign posted on the previous friday. Also, if the 'date to post the sign' falls on a holiday, you want to post the sign on the day prior to the holiday.

For example, if you are supposed to post on a sign on June 10th, 2006 (which is a Saturday), you need to return June 9th (the previous Friday). If the 9th is a holiday, then you want the 8th. Essentially, you want the greatest date prior to (SuspensionDate-RefValue) that is NOT a holiday, and Not a weekend. Is this correct?

-George

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

 

[sanctified]

Your last posting is correct George.
No postings are done on a weekend. All postings are done during the week. The weekends are taken into consideration. The weekday is passed into the SP if it is a Friday, then the weekend days are taken into account. This has already been worked out. All I need to do is to take into account a holiday date.
Yes, the bit in blue is the problem. The statement in itself WILL return more than one record but as part of the DATEDIFF function/statement it won't. Think of the Holiday table as a lookup table. I have nno parameters I can pass to it - I am referencing it.

Thanks for your help.

 

[gmmastros]

Let's setup some dummy data for testing purposes.

Declare @Temp Table(SuspensionDate DateTime, DaysNotice Integer)

Insert Into @Temp Values('2006-07-9', 10)
Insert Into @Temp Values('2006-07-10', 10)
Insert Into @Temp Values('2006-07-11', 10)
Insert Into @Temp Values('2006-07-12', 10)
Insert Into @Temp Values('2006-07-13', 10)
Insert Into @Temp Values('2006-07-14', 10)
Insert Into @Temp Values('2006-07-15', 10)

Declare @HolidayDates Table (HolidayDate DateTime)

Insert Into @HolidayDates Values('2006-07-04')
Insert Into @HolidayDates Values('2006-12-25')

To simplify things, I put the suspension date and the days notice in the same table. Of course, your query will be more complicated than this, so you'll need to adjust accordingly.

Open a Query Analyzer window and copy/paste the code (shown above), then copy/paste this code below it.

Select SuspensionDate,
       SuspensionDate - DaysNotice As OriginalDateToPost,
       DateName(Weekday, SuspensionDate - DaysNotice) As OriginalWeekdayToNotice
From   @Temp

This will show the SuspensionDate and the original Date to post. Notice that it does not accomodate weekends. That's coming up next.

Select 	SuspensionDate,
	SuspensionDate - DaysNotice As PostingDate, 
	SuspensionDate - DaysNotice 
            + Case DateName(Weekday, SuspensionDate-DaysNotice)
            When 'Saturday' Then -1
            When 'Sunday' Then -2
            Else 0
            End As AdjustedForWeekend,
        DateName(Weekday, SuspensionDate 
            - DaysNotice 
            + Case DateName(Weekday, SuspensionDate-DaysNotice)
            When 'Saturday' Then -1
            When 'Sunday' Then -2
            Else 0
            End) As WeekdayAdjustedForWeekend
From   	@Temp

Notice that now we have a query that accomodates weekends because if a posting date appears on a Saturday or Sunday, we bump the posting date back tot he previous friday (by subtracting 1 or 2 days accordingly).

Now, here's the tricky part. Whatever date we found in the previous query shouldn't be a holiday. If it is, then we need to bump it back another day. Like this...

Select  A.SuspensionDate,
		A.AdjustedForWeekend,
		HD.HolidayDate,
        A.AdjustedForWeekend + Case When HD.HolidayDate Is Not NULL
             Then -1
             Else 0
             End
From	(
		Select 	SuspensionDate,
				SuspensionDate - DaysNotice As PostingDate, 
				SuspensionDate - DaysNotice 
		            + Case DateName(Weekday, SuspensionDate-DaysNotice)
		            When 'Saturday' Then -1
		            When 'Sunday' Then -2
		            Else 0
		            End As AdjustedForWeekend,
		        DateName(Weekday, SuspensionDate 
		            - DaysNotice 
		            + Case DateName(Weekday, SuspensionDate-DaysNotice)
		            When 'Saturday' Then -1
		            When 'Sunday' Then -2
		            Else 0
		            End) As WeekdayAdjustedForWeekend
		From   	@Temp
		) As A
		Left Join @HolidayDates As HD On A.AdjustedForWeekend = HD.HolidayDate

So far so good?

The problem is that this method is inherently flawed because it is prohibitively difficult. Sure, it'll probably work most of the time, but not always.

Suppose you are supposed to post on a Monday. You adjust for weekends, but since is was already a monday, no adjustments are made to this date. Then, you adjust for holidays. In this case, suppose it is a Holiday. The query above will 'bump' the date back by 1. So, we end up with a Sunday (which clearly isn't allowed). We could tweak the query again to account for that, but then we should check if the new date is also a holiday, bump again, check for weekend, bump again, ad nauseum...

Now, suppose you create a calendar table. In this calendar table, you should have EVERY date possible. Add a bit field for PostingAllowed. Then, all you need to do is join with this table and get the max date where PostingAllowed = true AND CalendarDate <= (SuspensionDate - DaysNotice). This will simplify the query and make your results more accurate. Like this...

[green]-- Dummy Data[/green]
Declare @Temp Table(SuspensionDate DateTime, DaysNotice Integer)

Insert Into @Temp Values('2006-07-9', 10)
Insert Into @Temp Values('2006-07-10', 10)
Insert Into @Temp Values('2006-07-11', 10)
Insert Into @Temp Values('2006-07-12', 10)
Insert Into @Temp Values('2006-07-13', 10)
Insert Into @Temp Values('2006-07-14', 10)
Insert Into @Temp Values('2006-07-15', 10)

[green]-- Let's create a calendar table[/green]
Declare @Calendar Table (CalendarDate DateTime, PostingAllowed Bit)
Declare @TempDate DateTime

Set @TempDate = '2006-01-01'

While @TempDate <= '2006-12-31'
  Begin
    Insert 
    Into   @Calendar(CalendarDate, PostingAllowed) 
    Values (@TempDate, 1)

    Set @TempDate = @TempDate + 1
  End

[green]-- Don't allow posting on the weekends[/green]
Update @Calendar 
Set    PostingAllowed = 0 
Where  DateName(Weekday, CalendarDate) In ('Saturday', 'Sunday')

[green]-- Don't allow posting on Holidays[/green]
Update @Calendar 
Set    PostingAllowed = 0 
Where  CalendarDate In ('2006-07-04', '2006-12-25')

[green]-- And now, for the query[/green]
Select T.SuspensionDate,
       T.SuspensionDate - DaysNotice As OriginalDateToPost,
       Max(C.CalendarDate) As DateToPost,
       DateName(Weekday, Max(C.CalendarDate)) As WeekdayToPost
From   @Temp T
       Inner Join @Calendar C
         On C.CalendarDate <= (T.SuspensionDate - DaysNotice)
Where  C.PostingAllowed = 1
Group By T.SuspensionDate, T.DaysNotice

Notice how simple this query is. Also notice that this follows the advice I gave you in my first post. I've learned from experience that this is the best method to accomplish the results you want. I sincerely hope this helps.

-George

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

 

[sanctified]

George,
Postings should NOT be allowed for dates of records in the calendar table. What we are saying in a nutshell is: If the posting date (suspensionstart - noticeperiod) falls on a date in the calendar table then post on the previous weekday.

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.

I'll see if I can crib anything from your code.
Thanks for your time. I'm sure we'll get there. As I said it is overcoming the subquery returning more than 1 record error. My routine needs revamping...