Go back a Year using year and Month fields 3

[Cpreston]

Hi

I have some tables with no dates in except Month and Year. These are 2 separate fields called CalenderYear and CalenderYear
Is it possible for me to set them somehow so they look back from todays date back 12 months some how. I have googled and looked for examples but beginning to think it may not be possible.

Any ideas please

Thanks

 

[JeffModen]

In case someone wants to play with some other methods, here's how I created the test table. You'll need to change the table name on my code above if you want to play with it against this data.

--
--===== If it exists, drop the Test Table to make reruns easier in SSMS
   DROP TABLE IF EXISTS #TestTable
;
--===== Create and populate the data with random constrained data on the fly.
 SELECT TOP (1000000)
         CalendarMonth = CONVERT(TINYINT ,ABS(CHECKSUM(NEWID())%12)+1)
        ,CalendarYear  = CONVERT(SMALLINT,ABS(CHECKSUM(NEWID())%22)+2000)
        ,QuantitySold  = CONVERT(INT     ,ABS(CHECKSUM(NEWID())%10)+1)
        ,ProductID     = CONVERT(INT     ,ABS(CHECKSUM(NEWID())%10)+49805)
        ,BranchID      = CONVERT(TINYINT ,ABS(CHECKSUM(NEWID())% 2)+1)
   INTO #TestTable 
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
--===== In the absense of all other knowledge of the real table, add an appropriate
     -- appropriate index for this problem.
 CREATE CLUSTERED INDEX PK_TestTable ON #TestTable (CalendarYear,CalendarMonth)
;
--===== Delete the February 2021 data to demo what a "zero" month reports as.
 DELETE FROM #TestTable WHERE CalendarMonth = 2 AND CalendarYear = 2021
;
;

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row