Query to calculate average cost

Question

Post reply

Query to calculate average cost

Vimal Ori

SSC Veteran

Points: 218
More actions
September 13, 2005 at 6:09 am

#165478

I am trying to calculate the average cost of a product. The base table contains date, number of units bought/sold, price.
I need to construct cumulative cost: Buy transaction increase cumulative cost by number of units × price. Problem lies in the fact that sell transactions have to reduce cumulative cost by the units × average price just prior to that transaction.
I cant figure out how to construct the SQL for this?
My SQL currently looks like this:
SELECT TDate, Units, Price,
(SELECT SUM(a.Units)
FROM Table1 a
WHERE (a.TDate < Table1.TDate)) as CumUnits,
(SELECT ?????)) As CumCost
FROM Table1
I cannot figure out how to construct the CumCost part.
Any help appreciated.
TIA

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can you post some sample data and the required output... will be hard to answer without that.

Vimal Ori SSC Veteran Points: 218 More actions · Answer 2

BaseTable:
TDate Units Price
30/06/2004 10,000 10
15/07/2004 2,500 11
25/07/2004 -1,100 12
31/07/2004 2,300 11.5
15/08/2004 -5,000 13
31/08/2004 1,500 12.5

Required Output

TDate Units Price CumUnits Cost Adj. CumCost AvgCost

30/06/2004 10,000 10 10,000 100,000 100,000 10

15/07/2004 2,500 11 12,500 27,500 127,500 10.2

25/07/2004 -1,100 12 11,400 -11,220 116,280 10.2

31/07/2004 2,300 11.5 13,700 26,450 142,730 10.4182...

15/08/2004 -5,000 13 8,700 -52,091 90,639 10.4182...

31/08/2004 1,500 12.5 10,200 18,750 109,389 10.7244...

I have added CumUnits, Cost Adj., CumCost & AvgCost here for clarity. In fact what is required is at 31/08/2004 there were 10,200 units with a cumulative cost of 109,389.

Calculation of fields:
CumUnits: straightforward incremental sum of units as of a date
Cost Adj: for buy (ie positive no. of units) =Units × Price
for sell = units × avg cost prior to sell
CumCost:incremental sum of Cost Adj. as of a date
AvgCost: CumCost / CumUnits

HTH

Phil Parkin SSC Guru Points: 247161 More actions · Answer 3

This gets you some of the way and may give you an idea about how to finish it off:

select b1.*, sum(b2.units) CumUnits

from basetable b1

join basetable b2 on b1.tdate >= b2.tdate

group by b1.tdate, b1.units, b1.price

CostAdj, AvgCost and CumCost - found myself going round and round - are you sure there's not a circular reference in there

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

This is what I have, but my results don't match up with yours (some maths error I guess).

Can you verify that this is what you need??

Declare @BT table (TDate smalldatetime not null, ProductID int not null, Units decimal (10,3) not null, Price decimal (10,3) not null, primary key clustered (TDate, ProductID, Units, Price))

Insert into @BT (TDate, ProductID, Units, Price) values ('06/30/2004', 12345, 10000, 10)

Insert into @BT (TDate, ProductID, Units, Price) values ('07/15/2004', 12345, 2500, 11)

Insert into @BT (TDate, ProductID, Units, Price) values ('07/25/2004', 12345, -1100, 12)

Insert into @BT (TDate, ProductID, Units, Price) values ('07/31/2004', 12345, 2300, 11.5)

Insert into @BT (TDate, ProductID, Units, Price) values ('08/15/2004', 12345, -5000, 13)

Insert into @BT (TDate, ProductID, Units, Price) values ('08/31/2004', 12345, 1500, 12.5)

Select B1.TDate, B1.ProductID, B1.Units, B1.Price, SUM(B2.Units) as CumUnits, B1.Units * B1.Price AS CostAdjust, SUM(B2.Units * B2.Price) AS CumCost, SUM(B2.Units * B2.Price) / SUM(B2.Units) AS AvgPrice from @BT B1 inner join @BT B2 on B1.ProductID = B2.ProductID and B2.TDate <= B1.TDate

group by B1.TDate, B1.ProductID, B1.Units, B1.Price

Vimal Ori SSC Veteran Points: 218 More actions · Answer 5

Phil/RGR: Thank you both for your ideas - I am studying the arithmetics of it now as you both seem to suggest that there either be a math problem or circular reference.

Indeed at first glance it seems to be a circular but it is only conditional. I'll test the maths again and revert.

Phil: Your SQL did give me an idea where I need to go.

RGR: Your SQL seems to have a problem with CostAdjust as you take it to be always Units × Price - which is not what is required in the case of sales. I think that a CASE construct will be appropriate here.

I'll post back my findings - sorry for the delay but I am in Mauritius in the GMT+4 time-zone.

Vimal

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

OK I see what you need now... and that query will be ugly as hell but I'll give it a shot.

TBC.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Now that I rethink of it. I would do it with a table variable. I'd get the first few columns into the table variable, then join to itself to calculate the final columns, it should be less overhead for the server that way.

Vimal Ori SSC Veteran Points: 218 More actions · Answer 8

I have come up with the following:

SELECT TDate, Units, Price,

(SELECT SUM(a.Units)

FROM Table1 a

WHERE (a.TDate < Table1.TDate)) as CumUnits,

(CASE WHEN Units > 0 THEN Units*Price ELSE Units*(

(SELECT SUM(b.Units*b.Price)

FROM Table1 b

WHERE b.TDate < TDate and Units >0)/(

SELECT SUM(c.Units)

FROM Table1 c

WHERE c.TDate < TDate and Units > 0)) END) As AdjCost

FROM Table1

I admit that this is a very ugly construct and that the overhead for the server would be high. This itself gives the base and I am using another SQL (I have created the above as a function) to aggregate the result of the above.

I do not understand the concept of a table variable; grateful if you could explain it to me or refer me to suitable reading material. Will try to research table variable in the meantime.

Many thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I'll try to finish my thaughts on this one tomorrow. Drop me a line if I forget to.

Query to calculate average cost

Cookies on SQLServerCentral