Query to calculate average cost

  • 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

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

  • 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

     

  • 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


  • 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

  • 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

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

    TBC.

  • 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.

  • 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

     

  • 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