crosstab table and group by row

[ngocham2001]

Hi everyone,
I am using PHP and Mysql to make a small inventory program. I have a problem with end_stock field. Detail:
I have 2 tables:
1. Receiving:
ID_Receive | ProductID | Date | Quantity | Price
1 | A | 01/07/2012 | 5 | 1500
2 | B | 02/07/2012 | 3 | 1100
3 | C | 03/07/2012 | 5 | 920
4 | A | 10/07/2012 | 5 | 1550
5 | C | 01/08/2012 | 4 | 1010
6 | A | 15/08/2012 | 3 | 1420
2. Deliveried
ID_Deliver| ProductID | Date | Quantity
1 | B | 02/07/2012 | 1 |
2 | A | 03/07/2012 | 1 |
3 | C | 04/07/2012 | 1 |
4 | A | 05/07/2012 | 1 |
5 | C | 01/08/2012 | 1 |
6 | A | 03/08/2012 | 1 |
7 | B | 04/08/2012 | 1 |
8 | A | 05/08/2012 | 1 |
Now, I need calculator stock_end of August-2012. The result table is
Product ID | Start Quant. | Price | Receiv Quant. | Price | Deliver Quant. | Price | End Quant. | Price|
A | 4 | 1500 | - | - | 2 | 1500 | 2 | 1500 |
A | 5 | 1550 | - | - | - | - | 5 | 1550 |
A | - | - | 3 | 1420 | - | - | 3 | 1420 |
B | 2 | 1100 | - | - | 1 | 1100 | 1 | 1100 |
C | 4 | 920 | - | - | 1 | 920 | 3 | 920 |
C | - | - | 4 | 1010 | - | - | 4 | 1010 |
Any help me? I can't display and calculator it?

[dinacristi]

This is a business problem first. You cannot have an report for both quantityes and prices. On a stock problem you need to calculate the quant first and then evaluate the prices by diferent methods (aquisition, last price average, market etc).
Anyway for your report you also need the "result" table from july.

You then must join those 3 tables by product (and price if you must) and then calculate the quantity. Result(n+1)= Result(n)+Recived(n)-Delivered(n). Where n is the current month.

select prodId,Result.quant,sum(Recived.quant), sum(Delivered.quant), Result.quant+sum(Recived.quant)-sum(Delivered.quant)
from Results(n-1)
left join Recived(n)
lect join Delivered(n)
group by prodId

of course Results(n-1),Recived(n),Delivered(n) are subselects.
Also you must treat the case where a new product is Recived so you must use isnull function or similar.

[ngocham2001]

You are so nice and thank you very much.
Any help me? Although I read "DaniWeb-Flavored Markdown Syntax" but I still can't post my discuss. Error about syntax of code. I paste my code into my discuss, and then I selected it and select Code button in toolbar. But it isn't complete with error. What did I wrong? Please help me! Thank you in advanced.

[LastMitch]

@ngocham2001

Can you show the code?

I am using PHP and Mysql to make a small inventory program.

This is more than a small inventory program, it's looks more like a e-commerce question.

Any help me? I can't display and calculator it?

You have to show the code in php and mysql in order to help you.

[ngocham2001]

OK, thank you Dinacristi and LastMitch.
Fisrt of all, I create a view to calculate start stock :

CREATE VIEW stockwithprice AS select receiving.ProductID AS ProductID,ID_receiving, sum(receiving.Quantity) AS Quantity,receiving.Price AS Price from receiving where ((month(receiving.Date) = 7) and (year(receiving.Date) = 2012)) group by receivingsparepart.ProductID,ID_receiving ;

And then, I crate a view to calculate received in current month:

CREATE VIEW receivingwithprice AS select receiving.ProductID AS ProductID,ID_receiving, sum(receiving.Quantity) AS Quantity,receiving.Price AS Price from receiving where ((month(receiving.Date) = 8) and (year(receiving.Date) = 2012)) group by receiving.ProductID,ID_receiving;

Create a view to calculate deliveried in current month:

CREATE VIEW replacecurrent AS select replacement.ProductID AS ProductID,sum(replacement.Quantity) AS ReplacementSUM from replacement where ((month(replacement.Date) = 8) and (year(replacement.Date) = 2012)) group by replacement.ProductID;

Finally, I combined 3 views into a select query:

select ProductID, Price,sum(if(Type='Stock',Quantity,0)) as Stock, sum(if(Type='Receiving',Quantity,0)) as Received, sum(if (Type='Deliveried',Quantity,0)) as Deliveried 
    from((SELECT stockwithprice.ProductID,stockwithprice.Quantity,Price,'Stock' as Type,receiving_ID
    from stockwithprice)
    union
    (select receivingwithprice.ProductID,Quantity ,Price,'Receiving' as Type,receiving_ID
    from receivingwithprice)
    union
    (select replacecurrent.AccountantCode,ReplacementSUM,0 as Price,'Deliveried' as Type,'' as receiving_ID
    from replacecurrent)) as b
    group by AccountantCode,receiving_ID

But result is:

Product ID | Price |  Stock | Received | Deliveried |
A | 1500 | 4 | - | - | 
A | 1550 | 5 | - | - |
A | 1420 | - | 3 | - |
A |  -   | - | - | 2 |
B | 1100 | 2 | - | - | 
B | -    | - | - | 1 |
C | 920  | 4 | - | - | 
C | 1010 | - | 4 | - | 
C | -    | - | - | 1 |

And I can't calculate end stock with the select. Any help me?

[dinacristi]

you do not need to union those tables you need to join them. Put the group by (product id) in the initial stock, recived, delivered views. without the reciving_id(you can put price but will mess up things).

You will get somethig like:

Initial stock:
A|9
B|2

Recived:
A|3
B|0

Deliverd:
A|2
B|1

Then (left)join those tables by prduct id. You can also add a join to your product catalog to get results for product with 0 inventory and 0 movement

select I.q, R.q, D.q, I.q+R.q-D.q

from Catalog
left join I
left join R
left join D

Then on result you get:
ProductId |Start Q |Recived Q|Ddelivered Q| End Q
A|9|3|2|10
B|2|0|1|1

[ngocham2001]

Thank you, But your result is missing prices. That is very important to calcualator amount...

[dinacristi]

http://accountinginfo.com/study/inventory/inventory-121.htm

you must first decide witch metod of inventory valuation you use LIFO/FIFO/MA etc, then you can easy calculate the value.

for the Weighted Average Cost (http://en.wikipedia.org/wiki/Weighted_average_cost#Weighted_Average_Cost) you can just ad a calculated column in the views "average price" witch meean the average (weighted) price for the initial/recived/delivered stock

So for product A

Initial 10
average value 100

recived1 q=10 price=110
recived2 q=30 price=102

delivered1 q=11 price=?

FIFO price=101 ->for first 10 is 100 and for the last 1 is 110 => 101 (average price)
LIFO price=102
MA => (10100+10110+30*102)/(10+10+30) = aprox 99

so you must firs decide witch method to use and then you can simply calculate the price and add it to your views.