set nocount on
go
CREATE TABLE dbo.Accounts
(
ID int IDENTITY(1,1) primary key, -- Primary Key defaults to a clustered index, needed for this to work
TransactionDate datetime,
Balance money,
RunningBalance money null
);
go
insert into Accounts(TransactionDate,Balance) values ('1/1/2000',100);
insert into Accounts(TransactionDate,Balance) values ('1/2/2000',101);
insert into Accounts(TransactionDate,Balance) values ('1/3/2000',102);
insert into Accounts(TransactionDate,Balance) values ('1/4/2000',103);
insert into Accounts(TransactionDate,Balance) values ('1/5/2000',104);
insert into Accounts(TransactionDate,Balance) values ('1/6/2000',105);
insert into Accounts(TransactionDate,Balance) values ('1/7/2000',106);
insert into Accounts(TransactionDate,Balance) values ('1/8/2000',107);
insert into Accounts(TransactionDate,Balance) values ('1/9/2000',108);
insert into Accounts(TransactionDate,Balance) values ('1/10/2000',109);
insert into Accounts(TransactionDate,Balance) values ('1/11/2000',200);
insert into Accounts(TransactionDate,Balance) values ('1/12/2000',201);
insert into Accounts(TransactionDate,Balance) values ('1/13/2000',202);
insert into Accounts(TransactionDate,Balance) values ('1/14/2000',203);
insert into Accounts(TransactionDate,Balance) values ('1/15/2000',204);
insert into Accounts(TransactionDate,Balance) values ('1/16/2000',205);
insert into Accounts(TransactionDate,Balance) values ('1/17/2000',206);
insert into Accounts(TransactionDate,Balance) values ('1/18/2000',207);
insert into Accounts(TransactionDate,Balance) values ('1/19/2000',208);
insert into Accounts(TransactionDate,Balance) values ('1/20/2000',209);
go
select * from dbo.Accounts;
go
print '-- Cross Join Query --';

set statistics io on

SELECT Acc.ID,CONVERT(varchar(50),TransactionDate,101) AS TransactionDate
  , Balance, isnull(RunningTotal,'') AS RunningTotal 
 FROM Accounts Acc  
   LEFT OUTER JOIN (SELECT ID,sum(Balance) AS RunningTotal 
                    FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance 
                           FROM Accounts A 
                             cross JOIN Accounts B 
                           WHERE B.ID BETWEEN A.ID-4 
                           AND A.ID AND A.ID>4)T
                    GROUP BY ID ) Bal 
     ON Acc.ID=Bal.ID;

set statistics io off
print '-- Cross Join Query --';
go
print '-- Update Query --';
set statistics io on

declare @var1 money,
        @var2 money,
        @var3 money,
        @var4 money,
        @var5 money;

update dbo.Accounts set
    @var5 = @var4,
    @var4 = @var3,
    @var3 = @var2,
    @var2 = @var1,
    @var1 = Balance,
    RunningBalance = isnull(@var1 + @var2 + @var3 + @var4 + @var5, 0);
set statistics io off
print '-- Update Join Query --';
go
print '-- Select After Update Query --';
set statistics io on
select * from dbo.Accounts;
set statistics io off
print '-- Select After Update Query --';
go
drop table dbo.Accounts;
go
set nocount off
go