Current year to date, last year to date and others

Question

I need to classify the date set as 'Cur. YTD', 'Lst. YTD' or 'Other'. Based on getdate () since the beginning of the year. I have a temporary table for testing that has one column named "calendar_date" of type DATETIME I came up with this logic and it seems to work. I'm just wondering if this approach is suitable in terms of performance or if something else could be better.

select calendar_date, case when (MONTH(calendar_date) < MONTH(getdate())) or (MONTH(calendar_date) = MONTH (getdate()) AND DAY(calendar_date) <= DAY(getdate())) then case when YEAR(calendar_date) = YEAR(GETDATE()) then 'CYTD' when YEAR(calendar_date) = YEAR(getdate()) - 1 then 'LYTD' else 'Other' end else 'Other' end as Tim_Tag_YTD from #temp1 

Answer 1

Your logic looks good and will work as is.

An alternative that simplifies a bit, assuming you have no future data.

 select calendar_date, Tim_Tag_YTD = case DATEDIFF(YEAR, calendar_date, GETDATE()) when 0 then 'CYTD' when 1 then 'LYTD' else 'Other' end from #temp1; 

In the case of your logic, you explicitly put future data in "Other", which can also be done like this:

 select calendar_date, Tim_Tag_YTD = case when calendar_date > GETDATE() then 'Other' else case DATEDIFF(YEAR, calendar_date, GETDATE()) when 0 then 'CYTD' when 1 then 'LYTD' else 'Other' end end from #temp1; 

Answer 2

Sometimes something unintuitive is faster. Something like this might be worth it.

 set variable @FirstOfLastYear to Jan 1 of last year using sql server date functions set @FirstOfThisYear = DateAdd(year, 1, @FirstOfLastYear) select 'last year' period , whatever else you need from #temp1 where calendar_date >= @FirstOfLastYear and calendar_date < @FirstOfThisYear union select 'this year' period , whatever else you need from #temp1 where calendar_date >= @FirstOfThisYear and calendar_date < getDate () union select 'other' period , whatever else you need from #temp1 where calendar_date <= @FirstOfLastYear or calendar_date > getdate() 

You will never know if you do not try.