SQL query for inventory management

Question

Hope I can explain the problem I'm having trouble with.

I have to write a stepwise methodology using pseudocode/SQL query to auto generate a list of products/items with low stock/expiry from the inventory database.The list must be updated at 12 a.m. daily.

I tried this

CREATE EVENT IF NOT EXISTS update_table
ON SCHEDULE EVERY 1 DAY STARTS '2022-05-22 00:00:00'
ON COMPLETION PRESERVE ENABLE

Do

Select inventory.products from inventory where inventory.stocks < 
inventory.required_stocks.

Answer 1

Your stated requirement is to run some sort of report very soon after the beginning of each calendar day.

The next question you must answer is this: What will you do with that report? Will you simply drop it into "low_stock" table someplace in your database? Will you format it into an email message and send it to your purchasing department? It will be difficult to make "pseudocode" for your requirement without first analyzing the overall business process you intend to enhance.

Various RDBMS systems have ways of doing scheduled things at particular times of day. You've shown the EVENT setup provided by MariaDB / MySQL. SQL Server has their "Jobs" system. postgreSQL has the pg_cron extension. Yo

The thing is, you can't just do SELECT operations from within these scheduled database actions: the result sets have noplace to go from that context. You can do CREATE TABLE midnight_run AS SELECT whatever ... to place the results in a table. But then the results are in another table.

If you want to get the results out of the DBMS, you'll need a UNIXish cron job or a Windowsish scheduled task running an appropriate application at midnight each day.

Pro tip Do your best to avoid scheduling stuff for precisely midnight. Many things run then. If you wait until a couple of minutes after the hour, your code is less likely to contend with other midnight code.