How can I manage a FIFO queue in a 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 an SQL query that selects the correct price?

In the example: if I have 3 elements in for 75, and then 3 elements 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 a price request for X and Y? They should use the price from the third and fourth row. In the example, is there a way to SELECT the third row in the In -table? I can not use auto_increment as an identifier for the row "third", because in the tables a message will also be displayed for other elements. Lines will not be deleted, they will be saved to explain the reasons.

SELECT Price FROM In WHERE ...?

NEW :

 +----+ | 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 :

 +----+ | 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

Reading the comments that you say that you want to add an automatic increment or date field to find out the correct position of each line. Once you add this, I would recommend adding another row to the processed table, which is automatically set to false when the row is added to the table. Any lines that were copied to OUT already have a set value for the processed file.

 +----+ | 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] 

As soon as the line is wrapped to OUT, you simply UPDATES the processed field of this line true.

Answer 2

I do not see a simple request that will help here. To simulate FIFO in SQL, I would look at three tables: OPERATION, OUT and FIFO. OPERATION is actually a transaction log, and the FIFO table is the FIFO state, and OUT is the responses from FIFO.

You update CURRENT with operations (adding and deleting elements) when they enter the OPERATION table and process requests to output an element to the OUT table, decreasing the values ​​in FIFO and, if necessary, deleting entries from the FIFO table.

Even then, I do not see a simple request for processing all this, since there is a need to request the first record to see enough for each operation, the corresponding update of this record and the request for additional records where the operation cannot be performed. My level of SQL capabilities does not lead me to a simple solution to this, it represents the business logic for me and rises to this level.