SQL ROW_NUMBER function

Paul 61 Reputation points
2022-01-26T15:59:02.253+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2022-01-26T17:09:03.733+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-01-26T17:20:46.963+00:00
    0 comments No comments