Using SQL LAG() to predict Stock

This was a simple scenario that made me use LAG() for the first time!

Scenario

The client wants if the quantity of a product he has in STOCK is enough to supply all the orders he has next month. He knows how many units he has in STOCK right now and how many orders he has next month. This was the table client had:

This table had the consumption and stock after consumption for each month until today.

The goal was to have one more line for the next month:

We decided to use the LAG() function for this situation to build a table based on consumption like the client already had.

LAG() — This function allows you to compare a row to any of the rows preceding it. (Learn more about SQL Window Functions)

We had the consumption for August (40) and we knew that today's stock was 20. So we used LAG() to get stock value from the previous line and take the consumption total to see if we have enough stock to supply all the orders. In this case, we have orders of 40 and only a stock of 20, we will need an additional 20 to supply all orders next month.

Query:

The client can now see in a PowerBI dashboard how many units of stock he needs to buy to supply the next months and never fail the orders.

Thanks for reading, have a nice week!