Click here to Skip to main content
14,694,667 members
Home » Discussions » Database
   

Database

 
AnswerRe: UPDATE Multiple tables on one filed. Pin
Shameel14-Jul-11 1:09
professionalShameel14-Jul-11 1:09 
AnswerRe: UPDATE Multiple tables on one filed. Pin
smcnulty200014-Jul-11 23:39
Membersmcnulty200014-Jul-11 23:39 
AnswerRe: UPDATE Multiple tables on one filed. [modified] Pin
Niladri_Biswas18-Jul-11 3:12
MemberNiladri_Biswas18-Jul-11 3:12 
QuestionCalculate and display a progressive balance Pin
Rameez Raja11-Jul-11 21:48
MemberRameez Raja11-Jul-11 21:48 
AnswerRe: Calculate and display a progressive balance Pin
Corporal Agarn12-Jul-11 5:40
professionalCorporal Agarn12-Jul-11 5:40 
AnswerRe: Calculate and display a progressive balance Pin
smcnulty200014-Jul-11 23:34
Membersmcnulty200014-Jul-11 23:34 
GeneralRe: Calculate and display a progressive balance Pin
Rameez Raja16-Jul-11 23:49
MemberRameez Raja16-Jul-11 23:49 
GeneralRe: Calculate and display a progressive balance Pin
smcnulty200018-Jul-11 14:36
Membersmcnulty200018-Jul-11 14:36 
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 compare the examples to what you are trying to do and make something out of this.

You should be able to paste each of the above into it's own window in management studio and you can see how they work better. Don't put them in the same window, of course, but I'm guessing you know that one.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...

QuestionSelecting multiple columns from several tables without using JOIN Pin
Dominick Marciano11-Jul-11 5:41
professionalDominick Marciano11-Jul-11 5:41 
AnswerRe: Selecting multiple columns from several tables without using JOIN Pin
Blue_Boy11-Jul-11 5:55
MemberBlue_Boy11-Jul-11 5:55 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
David Mujica11-Jul-11 6:28
MemberDavid Mujica11-Jul-11 6:28 
AnswerRe: Selecting multiple columns from several tables without using JOIN Pin
kmoorevs11-Jul-11 11:08
Memberkmoorevs11-Jul-11 11:08 
AnswerRe: Selecting multiple columns from several tables without using JOIN Pin
Shameel11-Jul-11 22:30
professionalShameel11-Jul-11 22:30 
AnswerRe: Selecting multiple columns from several tables without using JOIN Pin
David Skelly12-Jul-11 4:54
MemberDavid Skelly12-Jul-11 4:54 
AnswerRe: Selecting multiple columns from several tables without using JOIN Pin
AnnieMacD14-Jul-11 7:24
MemberAnnieMacD14-Jul-11 7:24 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
Shameel14-Jul-11 19:52
professionalShameel14-Jul-11 19:52 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
smcnulty200014-Jul-11 23:46
Membersmcnulty200014-Jul-11 23:46 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
Shameel15-Jul-11 3:22
professionalShameel15-Jul-11 3:22 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
AnnieMacD15-Jul-11 3:45
MemberAnnieMacD15-Jul-11 3:45 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
Shameel15-Jul-11 8:50
professionalShameel15-Jul-11 8:50 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
AnnieMacD15-Jul-11 8:56
MemberAnnieMacD15-Jul-11 8:56 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
smcnulty200015-Jul-11 6:39
Membersmcnulty200015-Jul-11 6:39 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
Shameel15-Jul-11 8:48
professionalShameel15-Jul-11 8:48 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
smcnulty200015-Jul-11 9:08
Membersmcnulty200015-Jul-11 9:08 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
AnnieMacD15-Jul-11 3:44
MemberAnnieMacD15-Jul-11 3:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.