I have a table containing all dates of the last four years (2018 - 2021) and I would like to have a column with a chronological order based on the last twelve months. This is what the table currently looks like:
| DATE | YEAR | MONTH | MONTH_NAME |
|---|---|---|---|
| 2021-10-03 | 2021 | 10 | October |
| 2021-10-02 | 2021 | 10 | October |
| 2021-10-01 | 2021 | 10 | October |
| 2021-09-30 | 2021 | 9 | September |
And now I like to add another column that orders all records based on the last twelve months. Today is in October 2021 so the last twelve months would be October 2020 - September 2021 and the correct order for each month no matter which year should be:
| MONTH | ORDER |
|---|---|
| October | 1 |
| November | 2 |
| December | 3 |
| January | 4 |
| February | 5 |
| March | 6 |
| April | 7 |
| May | 8 |
| June | 9 |
| July | 10 |
| August | 11 |
| September | 12 |
CodePudding user response：
In order to map the months as you asked for, you need to use [ORDER] = ((DATEPART(MONTH, DATE) 2) % 12) 1
For example,
DECLARE @Factor INT = 12 - DATEPART(MONTH, GETDATE())
;With MyCTE AS (
SELECT TOP 200 D = DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (select null)), '2000-09-01')
FROM sys.all_objects
)
-- I am using the above CTE only to create some data for the sample.
-- It represent the data from your table
-- If you can provide DDLD DML then we can present it with your sample data
--> here we do the map betwen the date and the requested ORDER column
SELECT D, DATEPART(MONTH, D), [ORDER] = ((DATEPART(MONTH, D) @Factor) % 12) 1
FROM MyCTE
GO
