カウントアップして日またぎでリセットするSQL

1.テーブルを作ります

create

create table indexT
(
    indexNo int not NULL,
    UpdateDatetime datetime,
    Primary Key( indexNo )
);

2.SQLを実行します。

count_up

update [indexT] set 
[indexNo] = ( case when CAST([UpdateDateTime] as DATE) = CAST(getdate() as DATE) then [indexNo] + 1 else 1 end ), 
[UpdateDateTime]= getdate();

insert into [indexT] ([indexNo], [UpdateDateTime]) 
select 1 as indexNo, getdate() as UpdateDateTime 
where NOT EXISTS (select 1 from indexT);

select [indexNo], [UpdateDateTime] from [indexT];