SQL Query Help

[Ianvn]

Please look at the following scenario.
Assume I have a transaction table with the following data

Date Type Item Qty Purchase Price
2016-01-01 Buy ABC 10 500
2016-01-10 Buy ABC 2 90
2016-08-01 Sell ABC 12 650
2016-09-05 Buy ABC 13 600

I want to get the avg purchase price of item ABC for current in stock items using a sql query.
So the answer in end should be something like this

Item Qty Purchase Price PricePerItem
ABC 13 600 46.15

In this example it will only be for the last row (row 4) since all other bought stock was sold.
But not all items will always be sold, so I need to make sure to keep the purchase price of all unsold items in mind.
Items work on first in first out basis.

Is this even possible to do in a single query?

 

[Batista]

Bad table design,

Buy and sell should be denoted by + and - (QTY field)so that you can just add up the numbers...
Now you have to do extra logic to get that.

CASE when item = 'buy' qty end
CASE when item = 'sell' qty*-1 end

 

[diabolus]

Many ways to do that, just off the top of my head, here's one option. The only reason i have the nested query is to accomodate an automatic "final qty in stock" calc, i do that with the "-1" multiplier when they type = 'Sell' . You can obviously build these CASE statement inside one select statement too, but it gets difficult to read and manage.

SELECT
subtable.item,
avg(subtable.purchase_price) as 'avg_pur_price',
avg(subtable.price_per)item) as 'avg_price_per_item',
sum(subtable.qty_moved) as qty_available
FROM
(
SELECT
item,
(purchase_price / qty) END as 'price_per_item'
qty_moved = CASE WHEN type = 'Sell' THEN qty*-1 ELSE qty END,
purchase_price
FROM transaction_table
WHERE item = 'ABC'
) as subtable


GROUP BY subtable.item

 

[Necropolis]

Many ways to do that, just off the top of my head, here's one option. The only reason i have the nested query is to accomodate an automatic "final qty in stock" calc, i do that with the "-1" multiplier when they type = 'Sell' . You can obviously build these CASE statement inside one select statement too, but it gets difficult to read and manage.

SELECT
subtable.item,
avg(subtable.purchase_price) as 'avg_pur_price',
avg(subtable.price_per)item) as 'avg_price_per_item',
sum(subtable.qty_moved) as qty_available
FROM
(
SELECT
item,
(purchase_price / qty) END as 'price_per_item'
qty_moved = CASE WHEN type = 'Sell' THEN qty*-1 ELSE qty END,
purchase_price
FROM transaction_table
WHERE item = 'ABC'
) as subtable


GROUP BY subtable.item

Now you've gone and done his homework for him :/

 

[diabolus]

Now you've gone and done his homework for him :/

Heh, i was feeling generous today

 

[Ianvn]

Bad table design,

Buy and sell should be denoted by + and - (QTY field)so that you can just add up the numbers...
Now you have to do extra logic to get that.

CASE when item = 'buy' qty end
CASE when item = 'sell' qty*-1 end

For the purpose of the example I showed buy and sell. Its more a transaction type that an actual field.
This is not the actual table design otherwise having the item name "ABC" would have been a bad design as well.

 

[Ianvn]

Thank for the reply.
I will have a look at this a bit later.
...

I had a look but it seems that the final answer I get to is not correct.
Using the query from diabolus I get to a final answer of R 48.83 which is not correct.

The answer should be R46.15

The reason for that is.
I am running the query today for all items that are currently in stock.
If you look at 2016-08-01 I sell all items which brings me to 0 stock level.
Now on 2016-09-05 I purchase 13 items which means the average price should only be calculated on the last item which is 600 / 13 = 46.15
But like I mentioned before. This example is very simplified and there might have been items left over on the 8th after selling.

This is something I have been playing around with at home and I just don't seem to be able to get to the correct answer running 1 query.

 

[XennoX]

Something about running totals comes to mind when I think about that problem. For that, you're going to need a calendar to join to.

Additionally, Microsoft SQL Server 2012 (perhaps 2008 R2) has the SUM(<expression>) OVER (ORDER BY <columns> PARTITION BY <columns>) functionality that does the running totals calculation.

 

[Batista]

For the purpose of the example I showed buy and sell. Its more a transaction type that an actual field.
This is not the actual table design otherwise having the item name "ABC" would have been a bad design as well.

So just build the transaction type into the amounts then by using + and -.Yeah I figured ABC isnt an actual item lol

 

[[)roi(]]

As mentioned; you should look into an appropriate design for your table(s).

Usually there's a definitive separation between purchases, sales and inventory. As for determining cost: weighted average, the calendar as Xennox mentioned becomes important. A simplistic single table scenario is always going to fraught with issues.

In a typical business scenario, your clients would probably want to factor in operating costs, losses, ... when it comes to determining cost price, meaning the calculation of weighted average cost would never be tied directly to a transaction but rather a period close e.g. trading month.

As for target margin (assuming that's your end goal), you'd probably like most retailers want to absorb some increases and benefit from other decreases without impacting shelf price (i.e. present price stability), yet have the system advise you to raise prices when it difficult to maintain your target margin (more typically during month end close / reconciliation, or infrequently during day close).

 

[Spacerat]

For this scenario you typically need a table to hold the current totals. This gets updated (MERGE) with each transaction. The transaction table becomes an audit log for your current totals. But yes Qty must be + and - so you can SUM()

 

[Ianvn]

For this scenario you typically need a table to hold the current totals. This gets updated (MERGE) with each transaction. The transaction table becomes an audit log for your current totals. But yes Qty must be + and - so you can SUM()

This is exactly what I ended up doing.
I also went and finally fixed up my table structures a bit.