Who needs loops anyway

fooraker

I have had such fun reading posts on this site.So, I give a bit of the joy back.

I have inherited swathes of code from my predecessor. It all works, but you have to look at it with a real big squint to understand what is going on half of the time.

This snippet is the best I have come accross to date, and I got some history on it too, from a colleague.

My man has a requirement, using SQL, to determine the previous (UK) business date from an input date. He was originally given access to a table that listed all dates for a long way into the future for the UK. The table contains a bit field, which is set to 1 for all non-business days. Given such a table, I would have thought that a simple SELECT/MAX combination would do the trick.

Something like this:

SELECT MAX(date) FROM dates WHERE date < @inputdate AND flag = 0;

Nope; too easy.

What he did was to remove all the weekend entries and leave just the public holidays. Ok, so now I am thinking there ought to be a loop in the code to move backward across weekends and the public holidays. Nope; way too obvious.

Here is his final solution, reproduced in its entirety (no comments are in the original code, of course):

create proc dbo.Holiday_getPrevBusDate( @prevBusDate datetime output, @date datetime = null ) as
begin

    set datefirst 1

    declare @dayOfWeek int

    if ( @date is null ) select @date = getdate()

    select @date = convert( varchar, @date, 106 )

-- Section 1
    select @dayOfWeek = datepart( weekday, @date )
    if ( @dayOfWeek = 1 )
        select @prevBusDate = dateadd( day, -3, @date )
    else if ( @dayOfWeek = 7 )
        select @prevBusDate = dateadd( day, -2, @date )
    else
        select @prevBusDate = dateadd( day, -1, @date )

-- Section 2
    if ( exists ( select * from Holiday where Country = 'UK' and Holiday = @prevBusDate ) )
    begin

        select @prevBusDate = dateadd( day, -1, @prevBusDate )

-- Section 3
        if ( exists ( select * from Holiday where Country = 'UK' and Holiday = @prevBusDate ) )
            select @prevBusDate = dateadd( day, -1, @prevBusDate )

-- Section 4
        select @dayOfWeek = datepart( weekday, @prevBusDate )
        if ( @dayOfWeek = 7 )
            select @prevBusDate = dateadd( day, -2, @prevBusDate )
        else if ( @dayOfWeek = 6 )
            select @prevBusDate = dateadd( day, -1, @prevBusDate )

-- Section 5
        if ( exists ( select * from Holiday where Country = 'UK' and Holiday = @prevBusDate ) )
            select @prevBusDate = dateadd( day, -1, @prevBusDate )

    end

end

"Section 1" is easy enough. It checks for Monday, Sunday to move back to Friday. Otherwise just move back one day. We are now on a weekday.

"Section 2" checks if we have moved back to a public holiday and moves back one day. Reasonable.

"Section 3" checks again if we have moved back to a public holiday and moves back one day. Why? Squint hard to figure it out. Its a special time of year. Remember, this is for the UK only.

"Section 4", back in the world of sanity, we do a straight forward weekend check to move Sat/Sun back to Friday.

"Section 5" checks again!! if we have moved back to a public holiday and moves back one day. Note that this final rule only exists where the initial move back landed us on a bank holiday (Section 2). You may have to squint before you see this special holiday also.

Amazingly, this does capture all current UK bank holiday rules. However, when our next King takes to the throne, if the coronation day is made a public holiday, they had better not make it the day before Good Friday!

RayS

well that's certainly an 'interesting' way of doing it, in that repeatedly bashing your head against a wall is an 'interesting' way to remove a tooth.