-1

After being tasked with trying to improve the way my company keeps the fuel inventory, i hit a wall. My SQL knowledge is pretty basic and i don't even know where to start.

A little background: The company i work for own and operate 5 power plants which run on diesel. Our fuel stores operate based on FIFO, where the fuel we purchased first is spent first.

Before i started this project, the company did a lot of this manually in excel files, a whole bunch of different ones, which has led to large issues with inconsistency.

I envision one SQL table where each fuel transaction are recorded, which includes any purchases and transfers made. So far so good, but what im struggling with is trying to sum up how much fuel of various price "layers" are left at the various plants.

This is the setup i came up with for the transaction table as well as some sample data. Row id is a unique index while transaction id is not unique, and is a way to keep transfers between plants (where the same transaction means two rows, one for the source and one for the target).

row_id transfer_id date type plant qty_in qty_out price
1 1 2022-05-01 Purchase plant_1 200 0 9000
2 2 2022-06-01 Consumption plant_1 0 100 -
3 3 2022-06-03 Transfer plant_1 0 50 -
4 3 2022-06-03 Transfer plant_2 50 0 -
5 4 2022-06-09 Purchase plant_1 150 0 5000
6 5 2022-07-15 Purchase plant_1 50 0 3000
7 6 2022-08-10 Consumption plant_1 0 150 -

The result i would like is something like this:

plant qty price
plant_1 50 5000
plant_1 50 3000
plant_2 50 9000

I hope i've managed to put into words what i am trying to achieve, and if its possible to implement. Lastly, i should also point out that i am not limited to any specific SQL server, but the ones we have been eyeing are MSSQL or MySQL simply because those are the ones i am aware of. If any of those is better suited, than that is what we will be using.

I have tried to implement the suggestion as found here but that gave me the wrong result as it depended heavily on the correct order of things in the table. It would subtract from the wrong price "layer", giving me the incorrect quantities.

<===EDIT===>

I tried implementing the answer from Lee Tom Here which works(ish). It gives me a inventory with the various price layers present, but it gives a wrong total_stock for the second layer. For plant_1 and product A it should be:

  • 3 @ 30
  • 1 @ 30

but i get:

  • 3 @ 30
  • 4 @ 30

I've created a fiddle with my attempt found here.

jonatan hellborg
  • 11
  • 3
  • So do you actually have match consumption data records to purchase records? If all you want to know is inventory remaining on each purchase, then each consumption is irrelevant. Add consumption by plant as a single total and create a running sum of purchases by date as well. The amount of purchase inventory left is `greatest(0,least(qty_in,running sum - total_consumption))`. If your DBMS does not have a greatest/least function, you can write one or use a Case When construct. The Case construct for this is a lot less readable. – Chris Maurer Aug 26 '23 at 21:54
  • Hi - you state “transaction id is not unique” but then in your data it is unique? Also, it’s very unclear (to me) how you get from your sample data to the expected output: why those two rows and not a different umber of rows; how are those two rows calculated; what happened to the 9000 price, why does it not seem to be relevant to your output? You talk about transfer between plants but then your sample data only has a single plant - so is there a use case you haven’t covered in your data? – NickW Aug 26 '23 at 22:22
  • 2 former FIFO questions I'm aware of [here](https://stackoverflow.com/a/71432273/2067753) (Postgres) and [here](https://stackoverflow.com/a/47129589/2067753) (MSsql) - you may want to include Postgres in your dbms considerations – Paul Maxwell Aug 27 '23 at 01:51
  • The way you use "transaction_id" as a term is going to confuse the heck out of most folks. I suggest you refer to it as what it is: a "**transfer_id**" instead. *(nb: The term "transaction" has a technical meaning in dbms speak.)* – Paul Maxwell Aug 27 '23 at 01:57
  • one other observation, using padded numbers (which must therefore be strings) as id's is wasteful. Just use integer or biginteger columns – Paul Maxwell Aug 27 '23 at 03:30
  • @ChrisMaurer I only need to match them so that each consumption removes qty from the right "price layer". I will look into this and see if this will achieve what i want. Much appreciated! – jonatan hellborg Aug 27 '23 at 04:19
  • @NickW i've expanded the post to include a second plant. As for how i end up with the two (now three) rows, that is due to the 9000 price "layer" being the first consumed one. So the three rows labeled as consumption would first consume the 9000 price layer, then move onto the layer for 5000 and so on. – jonatan hellborg Aug 27 '23 at 04:20
  • @PaulMaxwell Much appreciated, i will look into those two links and i have changed transaction_id into transfer_id to avoid confusion. – jonatan hellborg Aug 27 '23 at 04:20
  • Oh - I would also define the "date" column as a timestamp (or at least datetime in mssql) and to be honest would never call that column just "date" maybe trans_date or transaction_ts. – Paul Maxwell Aug 27 '23 at 07:44
  • @PaulMaxwell Noted. Date was used mainly as an example, where i was intending to use a datetime column in the final result. I will take the suggestion of naming into account however. – jonatan hellborg Aug 28 '23 at 16:46

0 Answers0