Last 12 months - With only year and monthID

Question

Year	MonthID	Value
2023	3	50
2023	1	80
2022	12	100
2022	9	80
2022	7	60
2022	6	80
2022	5	80
2022	2	80
2021	12	80
2021	8	80
2021	4	80
2020	4	80
2020	2	80

Using SQL Server 2022,I need to fetch Dynamic last 12 months of data from this.. Can you please help me

Answer 1

You didn't say what rdbms you're using. This works in SQL Server.

Step 1 is to turn your year/monthID into a date - CAST(CONCAT(year,'/', monthID,'/01') AS DATE)

Step 2 is to get first of month 1 year ago - DATEADD(YEAR, -1, DATEADD(DAY,1,EOMONTH(getdate(),-1)))

Then it's a simple select from where...

FIDDLE

;with cte as
  (select cast(concat(_year, '/',MonthID,'/01') as date) _date
        , _year
        , MonthID
        , value
  from data
  )
select _year
     , MonthID
     , value
  from cte
 where _date > DATEADD(YEAR, -1, DATEADD(DAY,1,EOMONTH(getdate(),-1)))

Returns:

_year	MonthID	value
2023	3	50
2023	1	80
2022	12	100
2022	9	80
2022	7	60
2022	6	80
2022	5	80

Answer 2

It's juts:

SELECT TOP 12 * 
FROM #TempTable
ORDER BY ROW_NUMBER() OVER (ORDER BY Year DESC, MonthID DESC);

Here is the sample data:

DROP TABLE IF EXISTS #TempTable;

CREATE TABLE #TempTable
(
    Year INT,
    MonthID INT,
    Value INT
);

INSERT INTO #TempTable (Year, MonthID, Value)
VALUES
(2023, 3, 50),
(2023, 1, 80),
(2022, 12, 100),
(2022, 9, 80),
(2022, 7, 60),
(2022, 6, 80),
(2022, 5, 80),
(2022, 2, 80),
(2021, 12, 80),
(2021, 8, 80),
(2021, 4, 80),
(2020, 4, 80),
(2020, 2, 80);

SELECT TOP 12 * 
FROM #TempTable
ORDER BY ROW_NUMBER() OVER (ORDER BY Year DESC, MonthID DESC);

---

If you are looking only for specific months, you can calculated your threshold date, based on your requirements, and the use it's year and month part to filter the data in your table:

DECLARE @TresholdDate DATETIME2(0)

SELECT TOP 1 @TresholdDate = DATEADD(YEAR, -1, DATEFROMPARTS([Year], [MonthID], 1))
FROM #TempTable
ORDER BY ROW_NUMBER() OVER (ORDER BY Year DESC, MonthID DESC);

SELECT *
FROM  #TempTable
WHERE [Year] > YEAR(@TresholdDate)
    OR ([Year] = YEAR(@TresholdDate) AND MonthID >= MONTH(@TresholdDate));