Hi,
Between a Year date range, I need following output
Year_Num, Alphabet
1901, A
1902, B
1903, C
..
..
..
1925, Y
1926, Z
1927, A
1928, B
1929, C
..
..
..
1951, Y
1952, Z
1953, A
1954, B
Question: I have tried the following to generate the sequece but how to rest/restart the alphabits cycle and start again from A to Z?
USE tempdb
GO
DECLARE @v_start_year smallint=1901, @v_end_year smallint=1954
;WITH cte_year_sequence(Year_seq)
as
(
select N
from dbo.tally
where N between @v_start_year AND @v_end_year
)
SELECT *
FROM cte_year_sequence
Note: You may use following code to generate dbo.tally
USE tempdb
GO
IF object_id('dbo.tally') IS NOT NULL
drop table dbo.tally
select row_number() over(order by a.name)-1 as N
into dbo.tally
from master.sys.columns a
Thanks.