I have a database which lists all stock, categories and a job booking system.
Problem is I am trying to add a stock checking feature that highlights stock that might be short for a job so it can be sub-hired in.
There are 4 main db tables that are relevant:
main fields that are relevant:
stock has stockID, catID which is linked to categories.
stock level has stocklevelID, affectstock, catID, jobID, startdate and enddate.
jobs has jobID, startdate, enddate.
What i need to do is calculate how many items each category has.
work out the dates each stocklevel is out where affectstock=1 and if it is short of stock to throw up a message saying short of x ???s on date ???.
im guessing along the lines of:
select * from stocklevel where jobID = $jobID GROUP BY catID
then for each
count(*) from stock where catID = $catID
get all dates between startdate and enddate
then im guessing i for each date i need to count which items are used on that date?
not quite sure where to go from here? or if i should modify the database slightly to make it easier?