Explanation: Year column increments by 1 after every 12 rows. Month column goes till 12 and then resets. Quarter column increments by 1 after every 3 rows but does not reset after year

CodePudding user response：

Try a tally table to get row numbers and use modulo (%) and integer division to derive the numbers

with cteTally as (
    SELECT TOP 100 ROW_NUMBER () OVER (ORDER BY NAME) -1 as MonthNumber 
    FROM sys.objects 
)
SELECT 
    CEILING(MonthNumber / 12)   2012 as CalYear
    , (MonthNumber % 12)   1 as CalMonth
    , 1   (MonthNumber / 3) as CalQuarter
FROM cteTally as T