set nocount on
 
use tempdb
 
if (object_id('x') is not null)
    drop table x
create table x (id int primary key identity(1, 1), date datetime, value bigint, total bigint)
declare @x table(id int, date datetime, value bigint, total bigint)
 
declare @d datetime
declare @i int
set @i = 1
 
while (@i < 10000)
begin
    insert into x (date, value)
        select getdate() + @i, round(rand() * 1000, 0)
    set @i = @i + 1
end
 
 
-- warm up
 
insert into @x
select TOP 100
     T1.id
    ,T1.date
    ,(select sum(value) from x)
    ,sum(T2.value) as total
from x T1 join x T2 on T2.date <= T1.date
group by T1.id, T1.date, T1.value
delete from @x
 
 
-- technique 1
 
set @d = getdate()
 
insert into @x
select 
     T1.id
    ,T1.date
    ,T1.value
    ,sum(T2.value) as total
from x T1 join x T2 on T2.date <= T1.date
group by T1.id, T1.date, T1.value
 
print replace('Inner Join = {0}ms', '{0}', datediff(ms, @d, getdate()))
select @i = COUNT(*) from @x
print replace('returned {0} rows', '{0}', @i)
print ''
delete from @x
 
 
-- technique 2
 
set @d = getdate()
 
insert into @x
select 
     T1.id
    ,T1.date
    ,T1.value
    ,(select sum(T2.value) from x T2 where T2.date <= T1.date) as total
from x T1 
 
print replace('Sub Select = {0}ms', '{0}', datediff(ms, @d, getdate()))
select @i = COUNT(*) from @x
print replace('returned {0} rows', '{0}', @i)
print ''
delete from @x
 
 
-- technique 3
 
set @d = getdate()
 
;with cte (id, pro_name, pro_qty, running_total) as
(
    select 
         id
        ,date
        ,value
        ,convert(bigint, value) 
    from x
    where id = 1
    
    union all
    
    select 
         x.id
        ,x.date
        ,x.value
        ,cte.running_total + x.value
    from cte join x on cte.id + 1 = x.id 
)
insert into @x
select * from cte
OPTION (MAXRECURSION 10000);
 
 
print replace('CTE = {0}ms', '{0}', datediff(ms, @d, getdate()))
select @i = COUNT(*) from @x
print replace('returned {0} rows', '{0}', @i)
delete from @x
 
 
GO
if (object_id('x') is not null)
    drop table x