Forum Discussion

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 int...

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