Dsum Issue

R

Richard Davies

Hi,

I am having issues real troubles getting a solution for the following, any
help would be really appreciated:-

I have a table like the following from which i want to calculate at each row
level - i.e. by date historically what the running to total for each company
is. For example i would like to be able to see that in January 2001 Company
938 and a total running allocation of X .

Date Allocation CompanyID
20/11/00 4.20833333333333 938
20/12/00 5.20833333333333 938
20/01/01 2.20833333333333 938
20/02/01 2.20833333333333 933
20/03/01 2.20833333333333 938
20/04/01 2.20833333333333 938
20/05/01 2.20833333333333 944
20/06/01 6.20833333333333 938
20/07/01 2.20833333333333 938
20/08/01 2.20833333333333 938
20/09/01 2.20833333333333 931
20/10/01 3.20833333333333 938
20/11/01 2.20833333333333 938
20/12/01 2.20833333333333 912
20/01/02 2.20833333333333 938


Any help would be appreciated!

regards

RD
 
M

Madhivanan

Is this you want?


select *, (Select sum(Allocation) from table where date=T2.date and
CompanyId=T2.CompanyId)as 'New Allocation'
from table T2

Madhivanan
 
R

Richard Davies

Hi,

Not sure, my desired output wold be something like:

Date Allocation Company RunningAllocation
1/1/2005 1.5 938 1.5
10/1/2005 1.0 934 1.0
1/2/2005 1.0 938 2.5
1/2/2005 1.0 934 2.0

Does that give you a clue.

regards
 
M

Madhivanan

How are you calculating Allocation and RunningAllocation from your
data?

Madhivanan
 
R

Richard Davies

Hi,

Allocation is just a number, RunningAllocation is the column i'd like to
calculate from the Allocation olumn on a per company basis.

Any ideas?
 
M

Madhivanan

Davies,

Try this

Alter table TableName add column id int identity


select Date,Allocation,CompanyId, (Select sum(Allocation) from @t where
id<=T2.Id and Company=T2.company)as 'RunningAllocation'
from @t T2

Alter table TableName Drop Column id

Madhivanan
 
M

Madhivanan

Davies,


Try this


Alter table TableName add id int identity


select Date,Allocation,CompanyId, (Select sum(Allocation) from @t where

id<=T2.Id and Company=T2.company)as 'RunningAllocation'
from @t T2


Alter table TableName Drop Column id


Madhivanan
 
R

Richard Davies

Hi,

Thanks for you help but i am really struggling to understand what you are
saying?
 
M

Madhivanan

Add a temporary column of identity field(As this is the unique value)
Execute the query
Drop that identity column

Alter table TableName add id int identity


select Date,Allocation,CompanyId, (Select sum(Allocation) from
TableName where
id<=T2.Id and CompanyId=T2.companyId)as 'RunningAllocation'
from TableName T2

Alter table TableName Drop Column id

Madhivanan
 
Top