How can I manage a FIFO-queue in an database with SQL?

Question

I have two tables in my database, one for In and one for Out. They have two columns, Quantity and Price. How can I write a SQL-query that selects the correct price?

In example: If I have 3 items in for 75 and then 3 items in for 80. Then I have two out for 75, and the third out should be for 75 (X) and the fourth out should be for 80 (Y).

How can I write the price query for X and Y? They should use the price from the third and forth row. In example, is there any way to SELECT the third row in the In-table? I can not use auto_increment as identifier for i.e. "third" row, because the tables will contain post for other items too. The rows will not be deleted, they will 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   | <- ?
+-------------+----------+-------+

Answer 1

I can't see a simple query that would help here. To simulate a FIFO in SQL I would look to three tables, OPERATION, OUT and FIFO. OPERATION is effectively a log of the transactions and the FIFO table is the FIFO state and OUT is the responses from the FIFO.

You would update CURRENT with the operations (adding and removing items) as they come into the OPERATION table and process requests for item 'out's into the OUT table decrementing the values in the FIFO and where necessary removing the records from the FIFO table.

Even then I don't see a simply query to process the whole thing as there's a need to query the first record to see if there is sufficient quantity for each operation, updating of that record appropriately and querying of additional records where the operation cannot be fulfilled. My level of SQL ability doesn't lead me to a simple solution to that, it constitutes business logic to me and gets lifted into that tier.

Answer 2

Reading the comments you say that you are willing to add a auto increment or date field to know the proper position of each row. Once you add this I would recommend adding one more row to the In table called processed which is automatically set to false when the row is added to the table. Any rows that have been copied to OUT already 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 find the next item to move to OUT you can do

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

Once the row is moved to OUT then you just UPDATE the processed field of that row to true.