How can I generate sequence in Sql month wise-CodePudding

I'm using query and get format like this

select CONCAT('D','/',FORMAT(GETDATE(),'MM-yy'),'/')

D/11-22/

now I add in this format one more thing is sequence which start number of 1,2,3,4... and when month was change then sequence is start from 1, like this example,

D/11-22/1
D/11-22/2
D/11-22/3
D/12-22/1
D/12-22/2

so how can I use query and get format like this ??

I try above query and getting that format

CodePudding user response：

checking your task, follows a query what I think could help you, I'm using partition to group row number by month and year

    WITH table1  AS (
    SELECT * FROM (
    VALUES
        ('2022-01-01', 'John Smith'), 
        ('2022-01-02', 'John Smith'), 
        ('2022-01-03', 'John Smith'), 
        ('2022-02-01', 'David Thompson'), 
        ('2022-02-02', 'David Thompson'),
        ('2022-02-02', 'David Thompson'),
        ('2022-02-03', 'Stacey Leigh'),
        ('2022-03-01', 'Stacey Leigh'),
        ('2023-01-01', 'John Smith'), 
        ('2023-01-02', 'John Smith'), 
        ('2023-01-03', 'John Smith'), 
        ('2023-02-01', 'David Thompson'), 
        ('2023-02-02', 'David Thompson'),
        ('2023-02-02', 'David Thompson'),
        ('2023-02-03', 'Stacey Leigh'),
        ('2023-03-01', 'Stacey Leigh')
    
    ) AS _ (MyDate,UserName)
)

select 
    CONCAT('D','/',FORMAT(CAST(myDate as date), 'MM-yy') ,'/', myMonthIndex)  as MyFormat
from 
     (
     select 
        t.*,
        (row_number() over (partition by Month(MyDate), year(MyDate) order by MyDate)) as myMonthIndex
      from 
        table1 as t
     ) t

the result

    MyFormat
    D/01-22/1
    D/01-22/2
    D/01-22/3
    D/01-23/1
    D/01-23/2
    D/01-23/3
    D/02-22/1
    D/02-22/2
    D/02-22/3
    D/02-22/4
    D/02-23/1
    D/02-23/2
    D/02-23/3
    D/02-23/4
    D/03-22/1
    D/03-23/1

Best Regards