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:

stock
categories
stocklevel
jobs

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?

please help