asked on

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

Thanks

Vitor Montalvão

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.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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 Kay

ASKER

thanks