Pfthb

The problem is this. If I have a quantity in any location, I want to perform the calculation below to each member of that job_no.

The idea is that if there's a quantity in loc3, the same quantity was previously in loc1 and loc2.

So, how do I get 10 in loc1 and loc2 may be another way to put it..?

select s.job_no, s.part, s.location, s.qty, 
 coalesce(ptime.setup_time, '-') as setup_time, 
 coalesce(ptime.cycle_time, '-') as cycle_time,
 ci.rate
 from stock as s join part_timings as pt
 on pt.part = s.part
 join locations as l on s.location = l.location
 left join part_timings as ptime on s.part = ptime.part
 and ptime.location = s.location
 join costs_internal as ci
 group by s.part, s.location
 order by s.part, l.stage



job_no | part | location | qty | setup_time | cycle_time | rate | total
123 p1 loc1 0 60 30 0.5 ?
123 p1 loc2 0 30 15 0.5 ?
123 p1 loc3 10 60 15 0.5 ?
123 p1 loc4 0 60 15 0.5 ?
123 p1 loc5 0 60 15 0.5 ?
123 p1 loc6 0 60 15 0.5 ?
123 p1 loc7 20 60 15 0.5 ?

calculation to get total:

coalesce(round((pt.cycle_time * s.qty * ci.rate) + 
(pt.setup_time * ci.rate), 2), '-')

EDIT:

I've added loc4 to loc7.

loc3 would need to have the calculation applied to loc1 and loc2 (qty 10).

loc7 would need to have the calculation applied to all locations that are before it (qty 20).

Maybe I'm not explaining it perfectly, struggle to get my intentions across sometimes with SQL!

Using a simplified version of your data...

select * from stock;

job_no qty location 
---------- ---------- ----------
123 0 loc1 
123 0 loc2 
123 10 loc3 
123 0 loc4 
456 0 loc1 
456 20 loc2 

You can use a sub-select to get the quantity for each job and join with it to get the stock for each job.

select stock.*, stocked.qty
from stock
join (select * from stock s where s.qty != 0) as stocked 
 on stock.job_no = stocked.job_no;

job_no qty location qty 
---------- ---------- ---------- ----------
123 0 loc1 10 
123 0 loc2 10 
123 0 loc4 10 
123 10 loc3 10 
456 0 loc1 20 
456 20 loc2 20 

stocked has the row for each job which is currently stocked.

Note that unless you've made a restriction, there may be more than one stocked row for a job.

loc7 would need to have the calculation applied to all locations that are before it (qty 20).

With this data...

sqlite> select * from stock order by job_no, location;
job_no qty location 
---------- ---------- ----------
123 0 loc1 
123 0 loc2 
123 10 loc3 
123 0 loc4 
123 0 loc5 
123 0 loc6 
123 20 loc7 
456 0 loc1 
456 20 loc2 

To accomplish this, instead of joining on the subselect do it on a per column basis else we'll get multiple values stocked locations. (There's probably also a way to do it with a join)

In order to make sure we select only previous locations (or our own) it's necessary to check that stock.location <= stocked.location. In order to ensure we get the closest one, order them by location and select only the first one.

select stock.*, (
 select stocked.qty
 from stock stocked
 where stock.job_no = stocked.job_no
 and qty != 0
 and stock.location <= stocked.location
 order by stocked.location asc
 limit 1
) as stocked_qty
from stock
order by job_no, location;

job_no qty location stocked_qty
---------- ---------- ---------- -----------
123 0 loc1 10 
123 0 loc2 10 
123 10 loc3 10 
123 0 loc4 20 
123 0 loc5 20 
123 0 loc6 20 
123 20 loc7 20 
456 0 loc1 20 
456 20 loc2 20 

This may be inefficient as a column subselect. It's important that job_no, qty, and location are all indexed.