I've two tables during my database, one for In and something for Out. They've two posts, Quantity and Price. How do i write a SQL-query that chooses the right cost?

In example: Basically have 3 products in for 75 after which 3 products in for 80. I Quickly have two out for 75, and also the third out ought to be for 75 (X) and also the 4th out ought to be for 80 (Y).

How do i write the cost query for X and Y? They ought to make use of the cost in the third and forth row. In example, can there be in whatever way to Choose the 3rd row within the In-table? I am unable to use auto_increment as identifier for i.e. "third" row, since the tables will contain publish for other products too. The rows won't be erased, they'll be saved for accountability reasons.

SELECT Price FROM In WHERE ...?

NEW database design:

+----+
| In |
+----+------+-------+
| Supply_ID | Price |
+-----------+-------+
|     1     |  75   |
|     1     |  75   |
|     1     |  75   |
|     2     |  80   |
|     2     |  80   |
+-----------+-------+
+-----+
| Out |
+-----+-------+-------+
| Delivery_ID | Price |
+-------------+-------+
|      1      |  75   |
|      1      |  75   |
|      2      |   X   | <- ?
|      3      |   Y   | <- ?
+-------------+-------+

OLD database design:

+----+
| In |
+----+------+----------+-------+
| Supply_ID | Quantity | Price |
+-----------+----------+-------+
|     1     |  3       |  75   |
|     2     |  3       |  80   |
+-----------+----------+-------+

+-----+
| Out |
+-----+-------+----------+-------+
| Delivery_ID | Quantity | Price |
+-------------+----------+-------+
|      1      |  2       |  75   |
|      2      |  1       |   X   | <- ?
|      3      |  1       |   Y   | <- ?
+-------------+----------+-------+

Reading through your comments ought to you say that you're prepared to give a auto increment or date area to understand the correct position of every row. When you add this I would suggest adding yet another row towards the In table known as processed that is instantly set to false once the row is put into the table. Any rows which have been replicated to OUT curently have their processed filed set to true.

+----+
| In |
+-----------+-----------+-------+-----------+
| AUtoId    | Supply_ID | Price | Processed |
+-----------+-----------+-------+-----------+
|     1     |     1     |  75   |     1     |
|     2     |     1     |  75   |     1     |
|     3     |     1     |  75   |     0     |
|     4     |     2     |  80   |     0     |
|     5     |     2     |  80   |     0     |
+-----------+-----------+-------+---------- +

Then to obtain the next item to maneuver to OUT that you can do

SELECT TOP 1 Supply_ID, Price 
FROM In WHERE Processed = 0
ORDER BY [Your Auto Increment Field or Date]

When the row is gone to live in OUT then you definitely just UPDATE the processed area of this row to true.

I can not visit a simple query that will help here. To simulate a FIFO in SQL I'd turn to three tables, OPERATION, OUT and FIFO. OPERATION is effectively a log from the transactions and also the FIFO table may be the FIFO condition and OUT may be the reactions in the FIFO.

You'd update Up-to-date with the procedures (adding and getting rid of products) because they enter into the OPERATION table and process demands for item 'out's in to the OUT table decrementing the values within the FIFO and where necessary getting rid of the records in the FIFO table.

Even so I do not visit a simply query to process the entire factor as there is a have to query the very first record to ascertain if there's sufficient quantity for every operation, upgrading of this record properly and querying of more records in which the operation can't be satisfied. My degree of SQL ability does not lead me to some simple means to fix that, it comprises business logic in my experience and will get lifted into that tier.