Row number creation

[kasimacsys]

Hi,

I have a table like below

create table ##Test (Inv nvarchar(5), InvDate date)
insert into ##Test values ('1001','2024-01-01')
insert into ##Test values ('1001','2024-01-01')
insert into ##Test values ('1001','2024-01-01')
insert into ##Test values ('1001','2024-01-02')
insert into ##Test values ('1001','2024-01-02')
insert into ##Test values ('1001','2024-01-03')

select * from ##Test
drop table ##Test

I need a result set like below. how to achieve that

create table ##Result (RowNo int,Inv nvarchar(5), InvDate date)
insert into ##Result values (1, '1001','2024-01-01')
insert into ##Result values (1, '1001','2024-01-01')
insert into ##Result values (1, '1001','2024-01-01')
insert into ##Result values (2, '1001','2024-01-02')
insert into ##Result values (2, '1001','2024-01-02')
insert into ##Result values (3, '1001','2024-01-03')

select * from ##Result
drop table ##Result

Thanks

[#2]

Hi,
The information you provided can't understandable.
please provide correct output you want in details.
Regards
Arshad

[#3]

@kasimacsys , one way is to use a sub query, like

declare @Test as table (Inv nvarchar(5), InvDate date)
insert into @Test values ('1001','2024-01-01')
insert into @Test values ('1001','2024-01-01')
insert into @Test values ('1001','2024-01-01')
insert into @Test values ('1001','2024-01-02')
insert into @Test values ('1001','2024-01-02')
insert into @Test values ('1001','2024-01-03')

select *, (SELECT COUNT(DISTINCT InvDate) FROM @Test AS SUB WHERE SUB.InvDate <= T.InvDate) AS Cnt
from @Test AS T