question

I've used row_number to define the result where INV is not 0, and a separate query to assign NULL when INV is 0. Then simply UNION them together. Where there are identical dates for a given ID, then the order is not guaranteed e.g. 2,5 ,'2017/01/01' 2,2 ,'2017/10/01' Unless there is another column we could use to define an order (you seem to have relied on insert/display order)? declare @YourTable table (ID int, INV int, Dates date) insert into @YourTable (ID, INV, Dates) select 1,0 ,'2017/01/01' insert into @YourTable (ID, INV, Dates) select 1,1 ,'2017/02/01' insert into @YourTable (ID, INV, Dates) select 1,2 ,'2017/03/01' insert into @YourTable (ID, INV, Dates) select 2,5 ,'2016/05/01' insert into @YourTable (ID, INV, Dates) select 3,10,'2017/01/01' insert into @YourTable (ID, INV, Dates) select 2,0 ,'2016/04/01' insert into @YourTable (ID, INV, Dates) select 5,2 ,'2017/01/01' insert into @YourTable (ID, INV, Dates) select 2,5 ,'2017/01/01' insert into @YourTable (ID, INV, Dates) select 2,2 ,'2017/10/01' select ID, INV, Dates, row_number()over(partition by ID order by Dates) from @YourTable where INV0 union all select ID, INV, Dates, null from @YourTable where INV=0 The resulting column produces a integer, and it would be trivial to convert this to ordinals (1st, 2nd, 3rd, etc) if needed ID INV Dates ----------- ----------- ---------- -------------------- 1 1 2017-02-01 1 1 2 2017-03-01 2 2 5 2016-05-01 1 2 5 2017-01-01 2 2 2 2017-10-01 3 3 10 2017-01-01 1 5 2 2017-01-01 1 1 0 2017-01-01 NULL 2 0 2016-04-01 NULL (9 rows affected)