Any equivalent Sunday...

Welcome, Dabblers, and let me begin with a small gift for you.  Our time is measured in a series of overlapping cycles - 60 minutes, 24 hours, 7 days, months that vary from 28 to 31 days in a year of 365 give or take.  Thus, handing dates can be a royal pain.  Apparently we have the ancient Egyptians to blame for this mess, but as they are no longer around to sue, we just have to fight our way through.

I recently had a requirement to generate a report comparing a set of figures to the nearest equivalent day on the previous year.  For example, the business wanted to compare figures for a two Thursdays, so pick a Thursday this year (say 23-02-2012) and find the nearest Thursday to the same date on the previous year (i.e. 24-02-2012).  This function returns you the date of that 'nearest equivalent day'.

create FUNCTION [dbo].[funGetSameDayLastYear] 

     (
     @inputdate date, -- the date which you wish to compare

     @yearoffset int = -1 -- the offset in years, e.g. -1 is last year, 3 is 3 years ahead etc.

     )
RETURNS smalldatetime

AS

BEGIN

     declare @inputDateDayOfWeek int
     declare @equivalentDateOnOffsetYear date
     declare @equivalentWeekdayOnOffsetYear int
     declare @dayOffset int
     declare @dateAdjustment int
     declare @adjustedDay smalldatetime

     SET @inputDateDayOfWeek =  datepart(dw,@inputdate)
     SET @equivalentDateOnOffsetYear = DATEADD(YY,@yearoffset,@inputdate)

     SET @equivalentWeekdayOnOffsetYear = DATEPART(dw,@equivalentDateOnOffsetYear)
     SET @dayOffset = @inputDateDayOfWeek - @equivalentWeekdayOnOffsetYear

     -- there is probably formula for this

     -- but it escapes me

     SELECT @dateAdjustment =
           CASE
                 WHEN @dayOffset = 0 THEN 0
                 WHEN @dayOffset IN (1,-6) THEN 1
                 WHEN @dayOffset IN (2,-5) THEN 2
                 WHEN @dayOffset IN (3,-4) THEN 3
                 WHEN @dayOffset IN (4,-3) THEN -3
                 WHEN @dayOffset IN (5,-2) THEN -2
                 WHEN @dayOffset IN (6,-1) THEN -1
           END

     SET @adjustedDay = dateadd(day, @dateAdjustment,@equivalentDateOnOffsetYear )

     RETURN(@adjustedDay)

END