SQL CASE rolling date difference-CodePudding

How would I pull the annual rolling date difference within a case statement? Or, if there's a better method that would work. When the NXT Anniversary Date month is after the current month, then I need the date to roll to the next year. I need to see the next anniversary dates that would be coming up in 2022.

For example:

Below is the code I was using, which works great for the current year.

Declare @prevbiz as Date set @prevbiz = DateAdd(day,Case (Datepart(Weekday,Cast(GetDate() as Date))) 
                                                When 2 then -3
                                                Else -1
                                                End, Cast(GetDate() as Date));  
    
DECLARE @prev12MONTHS AS DATE set @prev12MONTHS  = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, DAY(@enddate)-1)
    
    SELECT
        ANNUALREVIEWDATE
    ,   'NXT Anniversary Date'  =  CASE WHEN DATEADD(yy, DATEDIFF(yy, ANNUALREVIEWDATE, @prevbiz), ANNUALREVIEWDATE)  <= @prev12MONTHS 
                                THEN DATEADD(yy, DATEDIFF(yy, ANNUALREVIEWDATE, @prevbiz) -1, ANNUALREVIEWDATE)
                                 ELSE DATEADD(yy, DATEDIFF(yy, ANNUALREVIEWDATE, @prevbiz), ANNUALREVIEWDATE)
                                 END
    
FROM [table]

CodePudding user response：

I believe you're just looking to decide whether the anniversary has already passed and should then be advanced to next year.

datefromparts(
    year(getdate())  
        case when datefromparts(2000, month(getdate()), day(getdate()))
               >= datefromparts(2000, month(X), day(X)) 
            then 1 else 0 end,
    month(getdate()),
    day(getdate())
)

You could also just compare month and day parts individually. Year 2000 is just an arbitrary year that happens to have a leap day.

CodePudding user response：

Based on my findings on your question, this maybe could help you.

with dates(annualAnniversary) as (

    select DateAdd(month,6,DateAdd(year,-5,cast(GetDate() as date)))
    union 
    select DateAdd(month,3,DateAdd(year,-4,cast(GetDate() as date)))
    union 
        select DateAdd(month,3,DateAdd(year,-1,cast(GetDate() as date))) 
    union 
        select DateAdd(month,5,DateAdd(year,-2,cast(GetDate() as date)))
    union 
        select DateAdd(month,2,DateAdd(year,-1,cast(GetDate() as date)))
    union 
        select DateAdd(month,8,DateAdd(year,-3,cast(GetDate() as date)))
    union 
        select DateAdd(month,0,DateAdd(year,0,cast(GetDate() as date)))
      

)
select annualAnniversary, 
      DateAdd(year,DATEDIFF(year,annualAnniversary, cast(GetDate() as date)),annualAnniversary) as needsToShow  
from dates

for excluding rows those not meet their own annual Anniversary, You can add this to the above code:

where DATEDIFF(year,annualAnniversary, cast(GetDate() as date))>0

Output with raw data: