Dsum Issue

[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

 

[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

 

[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

 

[Madhivanan]

How are you calculating Allocation and RunningAllocation from your
data?

Madhivanan

 

[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?

 

[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

 

[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

 

[Richard Davies]

Hi,

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

 

[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