September 13, 2005 at 6:09 am
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:
I cannot figure out how to construct the CumCost part.
Any help appreciated.
TIA
September 13, 2005 at 6:29 am
Can you post some sample data and the required output... will be hard to answer without that.
September 13, 2005 at 7:04 am
Required Output
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.
HTH
September 13, 2005 at 8:12 am
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
September 13, 2005 at 12:44 pm
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
September 14, 2005 at 12:05 am
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
September 14, 2005 at 6:37 am
OK I see what you need now... and that query will be ugly as hell but I'll give it a shot.
TBC.
September 14, 2005 at 9:20 am
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.
September 14, 2005 at 11:27 pm
I have come up with the following:
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
September 14, 2005 at 11:36 pm
I'll try to finish my thaughts on this one tomorrow. Drop me a line if I forget to.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy