Forum Discussion

kasimacsys's avatar
kasimacsys
Copper Contributor
Jul 16, 2024

Row number creation

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

  • Arshad440's avatar
    Arshad440
    Brass Contributor
    Jul 16, 2024

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

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jul 17, 2024

    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

Resources