SQL - Month and year columns, get last 6 months

Hi, this one is simple but havent had enough coffee.


I have 3 columns in table TABLE_1
ID , Month, Year

I need to get all IDs from past 6 months

using SQL


Sample Data
sample

Thanks
LVL 15
Ess KayEntrapenuerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT ID
FROM TabelName
WHERE CAST(year+RIGHT('00'+month,2)+'01' AS DATE) >= DATEADD(month,-6,GETDATE())

Open in new window

NOTE: Assuming that year and month are VARCHAR. If not, then they need to be converted.
Vitor MontalvãoMSSQL Senior EngineerCommented:
For the case year and month are numeric:
SELECT ID
FROM TabelName
WHERE CAST(CAST(year AS VARCHAR)+RIGHT('00'+CAST(month AS VARCHAR),2)+'01' AS DATE) >= DATEADD(month,-6,GETDATE())

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Never use functions on a table column if it can be avoided, as you will prevent any index seek (make it "nonsargable"), which should be valuable in this case  (that table should be clustered by year and month).  Instead, do whatever data manipulation you need to do to present the values to match the column as it exists in the table.

For example, in this specific case, see code below.  The CROSS APPLYs could be changed to variables or CTEs -- the point was just to assign meaningful names rather than the clutter the WHERE condition with a bunch of functions:

SELECT ID, Month, Year
FROM table_name
CROSS APPLY (
    SELECT GETDATE() AS todays_date, DATEADD(MONTH, -6, GETDATE()) AS previous_date
) AS assign_alias_names1
CROSS APPLY (
    SELECT
        CAST(YEAR(todays_date) AS char(4)) AS todays_year,
        CAST(MONTH(todays_date) AS varchar(2)) AS todays_month,
        CAST(YEAR(previous_date) AS char(4)) AS previous_year,
        CAST(MONTH(previous_date) AS varchar(2)) AS previous_month        
) AS assign_alias_names2
WHERE
    Year BETWEEN previous_year AND todays_year AND
    ((Year = previous_year AND Month >= previous_month) OR
     (Year = todays_year AND Month <= todays_month))
Ess KayEntrapenuerAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.