Yes. (BTW, this is not really a beginner's task you are attempting in case you are wondering.) Yes, but life gets easier if you have a row number of some kind. As your example shows there's no particular order to these entries; they all have the same date. There is nothing indicating which ones you want first. I've added single quotes to your example entries. If you added another column to indicate order you could have this query:
Declare @Table Table
(
row int, EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 1, 'Abc','07/03/2010',6000,0 UNION ALL
Select 2, 'xyz', '07/03/2011',1500,0 UNION ALL
Select 3, 'Rameez', '07/03/2011',2000,0 UNION All
Select 4, 'Abhijit', '07/03/2011',1000,0; --UNION ALL
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx
As you can see the number inserted with the information is working just fine. This next version uses an identity column in the table to do the same job so if you have a lot of entries you won't have to pre-set all of their numbers.
Declare @Table Table
(
row int identity(1,1), EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
(
EmployeeName
,joiningDate
,Amount
,Balance
)
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0; --UNION ALL
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx
In this last example you can see I'm using row_number() to artificially create a row number on the fly.
Declare @Table Table
(
EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0;
with CTE01 as
(select *
,ROW_NUMBER() over (order by joiningdate) row
from @Table )
select xx.*
,(select sum(Amount) from CTE01 xy where xy.row <= xx.row ) RunningTotal
from CTE01 xx
I went ahead and used a common table expression (CTE) here because row_number can be a little intransigent when you use it directly in the subquery, but it works just fine in the CTE. Hopefully this all makes enough sense that you can co
