How to produce increasing quarter, month and year output in SQL?

Question

Year	Month	Quarter
2012	1	1
2012	2	1
2012	3	1
2012	4	2
2012	5	2
2012	6	2
2012	7	3
2012	8	3
2012	9	3
2012	10	4
2012	11	4
2012	12	4
2013	1	5
2013	2	5
2013	3	5
2013	4	6
2013	5	6
2013	6	6
2013	7	7
2013	8	7
2013	9	7
2013	10	8
2013	11	8
2013	12	8

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

Answer 1

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

CalYear	CalMonth	CalQuarter
2012	1	1
2012	2	1
2012	3	1
2012	4	2
2012	5	2
2012	6	2
2012	7	3
2012	8	3
2012	9	3
2012	10	4
2012	11	4
2012	12	4
2013	1	5
2013	2	5
2013	3	5
2013	4	6
2013	5	6
2013	6	6
2013	7	7
2013	8	7
2013	9	7
2013	10	8
2013	11	8
2013	12	8