I’m sure we’ve all run into this kind of problem at one point in time or another. In this case, I needed to perform a cross join of financial data against the days of the week.

Write a SQL function to return the last N week days.

Here’s what I was able to come up with.

alter function GetLastNWeekDays 

(

    -- Add the parameters for the function here

    @n int  

)

returns 

@BusinessDays table 

(   

    [Date] datetime,

    [DayOfWeek] int

)

as

begin

    declare @currentDate datetime,

                @count int,

                @dayOfWeek int;



    -- Get just the date part of the current date. (Ex: 2011-07-05 8:05:32 AM => 2011-07-05 00:00:00)

    set @currentDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

    set @count = 0;

    

    while @count < @n    

    begin

        set @currentDate = DATEADD(dd, -1, @currentDate)

        set @dayOfWeek = DATEPART(dw, @currentDate)

        if (@dayOfWeek = 1 or @dayOfWeek = 7) continue      

        insert into @BusinessDays ([Date], [DayOfWeek]) values (@currentDate, @dayOfWeek)

        set @count = @count + 1

    end

    

    return 

end

go

When run this morning (Tuesday, July 5, 2011) with a parameter of 4, this is the returning result set.