SQL Script - Categorising event data by the date they occured

Question

Hi, I am relatively new to writing SQL scripts, I was wondering if anybody could help with this? I have an formula that I am applying to a SQL extract in Excel which essentially groups each event by the date that it happened. Id like to make this smarter and develop this into my SQL so this calculation is done before extracting.

I am only interested in events that have taken place in the last year as of today. So it doesnt work to group them by calendar month, as today may fall mid month, therefore the formula just looks back and groups by 'Month 1", "Month 2" etc. The formula is looking back over a 12 month period... "1" being those events in the first month of the year, "12" being events in the last month etc and anything prior to a year back is "Historical".

I have been advised by a colleague that a CASE WHEN statement may work in SQL for this but I am struggling to work out how to replicate this.
Any advice, I would be very grateful. See below for my workings in Excel.

Answer 1

If you need just the Month column from EVENT_DATE, then try this approach:

select EVENT_DATE, EVENT_DATA, COMBINED_EVENT_KEY,
    case 
        when EVENT_DATE < dateadd(month, -12, getdate()) then 'HISTORICAL'
        when EVENT_DATE < dateadd(month, -11, getdate()) then '1'
        when EVENT_DATE < dateadd(month, -10, getdate()) then '2'
        when EVENT_DATE < dateadd(month, -9, getdate()) then '3'
        when EVENT_DATE < dateadd(month, -8, getdate()) then '4'
        when EVENT_DATE < dateadd(month, -7, getdate()) then '5'
        when EVENT_DATE < dateadd(month, -6, getdate()) then '6'
        when EVENT_DATE < dateadd(month, -5, getdate()) then '7'
        when EVENT_DATE < dateadd(month, -4, getdate()) then '8'
        when EVENT_DATE < dateadd(month, -3, getdate()) then '9'
        when EVENT_DATE < dateadd(month, -2, getdate()) then '10'
        when EVENT_DATE < dateadd(month, -1, getdate()) then '11'
        else '12'
    end as Month
from MyTable

Show other details if you need something more.