SQL ROW_NUMBER function

Question

How to use the row_number function on month number on a date range spanning over several years and the starting date is somewhere in the middle of the year.s? In the dataset, I would like to see the date (every date), calendar year, month name, row_number month number. and the date in ascending order. For example, the starting date range is 31 May 2020 and the ending date is today. Thanks

31 May 2020..........1
1 Jun 2020..............2
2 Jun 2020..............2
3 Jun 2020..............2
4 Jun 2020.............2
.........
1 Jul 2020..............3

Answer 1

Declare @StartDate date = '20200531'
;With Numbers As
(Select 0 As Number, @StartDate As Date
Union All
Select Number + 1, DateAdd(day, 1, Date)
From Numbers
Where Date < Cast(GetDate() As date))
Select Convert(char(11), n.Date, 106) As Date,
DateDiff(Month, @StartDate, n.Date) + 1 As MonthNumber
From Numbers n
Order By n.Date
Option(MaxRecursion 0);

Tom

Answer 2

Use a calendar table, not row_number()

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/