I'm working on a query that stuck me for some hours. I have 4 tables that I need to query to get a result that I need to populate a flexgrid with.
Table Transactions contains (tID, data, clientID, operation) that I need.
Table TransactionsContent contains some columns that I need to sum conditionally.
(tID, productID, palletNumber, boxesNumber, qty, price) that I need
Table Clients contains identification data for clients, need to retrieve NAME
Table pallets contains (paletID, paletState). paletState is for condition.
table Transactions column operation is 'in' or 'out' depends of the operation. This is joined with table TransactionsContent witch contains in or out data as pallet number, number of boxes, qty, price as specified by table Transactions (column operation).
I need to multi join for getting the data like
data - the date of receiving
(sum(boxesNumber in)-sum(boxesNumber out))
(sum(qty in)-sum(qty out))
price - price of acquisition
My grid will show smth like:
productID pallet date boxes qty acquisitionPrice client 1 1 ... 150 1500 1.45 X 1 4 ... 100 1000 1.52 y 1 4 ... 50 500 1.48 Z
SELECT T.productID, (SUM(IIF(T.operation='IN', T.operation,0))-SUM(IIF(T.operation='OUT',T.operation,0)) AS boxes etc
I used MIN(data) for the acquisition date, as the date of receiving is always previous of that of sell.
But how can I doto get the price of acquisition (operation 'IN') as I need to use it in GROUP BY clause? It returns 2 rows, one with the sum(IN) and one with -sum(out). I can't get MIN(price) because sometimes you need to get rid of stuff and sell at a lower price that you bought...
I have tried even create separate tables for IN and OUT but the results are in different columns....
Any advice is highly appreciated!
IF(anyoneCanHelp, 'Thanks in advance','I will break smth')