Can I schedule a task to run at a specified interval in MySQL?
I have an inventory MySQL database. The structure is as follows:
table_1 fields: itmcode, avgcost
table_2 fields: itmcode(FK to table_1(itmcode)), quantity
The basis of the report is when I want the inventory valuation details item wise for a past date.
The avgcost and quantity fields is changed when a new purchase is posted into system. I can run a query to see the current stock valuation, but I want to be able to see the stock valuation at a previous date as well. How do I do that? For quantity, I can add the sales and deduct the purchases backwards from the current date until whatever date the report requires, but the avgcost is current since this gets updated each time a purchase is posted.
I was wondering if an automatic daily dump could be executed, similar to this:
SELECT itmcode, quantity, avgcost, (avgcost * quantity) as ttlval
FROM table_1
JOIN table_2 ON table_1.itmcode = table_2.itmcode
Is this task possible to schedule directly in MySQL or is there some other solution?