There is a table called ld_det which is having the details for different locations say for eg at location pitsburg 50 nut bolts(spare part) were sold on 12th June 2011"
Now there is another table tr_hist which is having details of each transaction like on 12th June 2011 following transaction was done for nut bolt
10 at 12:00pm for site pitsburg 10 at 1:00 pm for site pitsburg .... so on.
Now we are genrating a report that will show elapsed time
what is elapsed time?
Suppose if order has been taken by production site and spare part is not available at that particular but they know it will be available before the delivery date. so the time and date of that particular transaction is elapsed time.
for example there were 50 nut bolts available on production site and order was taken for 70 nut bolts
so the time of transaction when it crossed 50 is called elapsed time
so considering the above example suppose in ld_det on 12th june 2011 for pitsburg production site there were 5 coresponding rows in tr_hist.
So I will write the query in SQl as following
select l.ld_det_name, l.ld_det_spare, l.ld_det_date, elapsed_time(l.ld_date) from ld_det l inner join
tr_hist t on l.ld_date = t.tr_date where elapsed_time(l.ld_date) <> 0 where elapsed_time can be a subroutine/function/procedure which will calculate based on following logic
x number;
for eacr row in ld_det
x = ld_tranc
for each row in tr_hist where tr_part = ld_part and tr_date = ld_date
