GET current month plus last 13 month SQL-CodePudding

I would like to modify the query which will also include the data from this month.

SELECT TOP (14) Entity = 'Total_Group'
    , Scenario = 'Actual'
    , Date = TRIM(CAST(YEAR(DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY object_id) * -1, GETDATE())) as CHAR(4))
      ' P'
      CAST(MONTH(DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY object_id) * -1, GETDATE())) as VARCHAR(2)))
FROM [sys].[all_objects]

the result of this table

starts with 2022P12, but would like to include current month too so it should start with 2023P1

CodePudding user response：

Try this:

SELECT TOP (14) Entity = 'Total_Group'
    , Scenario = 'Actual'
    , Date = TRIM(CAST(YEAR(DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY object_id) * -1   1, GETDATE())) as CHAR(4))
      ' P'
      CAST(MONTH(DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY object_id) * -1   1, GETDATE())) as VARCHAR(2)))
FROM [sys].[all_objects]