Sql query to calculate first occurrence of a sales order not fulfilled by stock -

Sql query to calculate first occurrence of a sales order not fulfilled by stock -

i have 2 tables:

sales orders (so ) fields:part, due_date, qty part fields part , stock.

i trying write query produce first occurrence ( date - so.due_date) sales order (so.qty) cannot fulfilled stock.

this easy if there no stock i.e. part.stock=0 or if there 1 sales order part (so.qty > part.stock)

if there multiple sales orders want first 1 shown e.g.

part.part = box , part.stock = 250 so.part | so.due_date | so.qty box | 26/10/2014 | 100 box | 27/10/2014 | 100 box | 28/10/2014 | 100 * homecoming row box | 29/10/2014 | 100

i think need sub query or need utilize cte can't work out unless utilize loop. tables have thousands of parts , sales orders , trying run query possible.

many help

i assume learning exercise, no real business work way. anyway, here query want:

select * sales_order so1 due_date = (select min(due_date) sales_order so2 inner bring together part p on p.part = so2.part so1.part = so2.part , stock < ( select sum(quantity) sales_order so3 so3.due_date <= so2.due_date , so3.part = so2.part ) )

which have set working fiddle here: http://sqlfiddle.com/#!2/bd8ab5/1 there assumptions such 1 order per date, believe answers question.

sql