We have to make dashboard for a production site
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
if x > 0
x = x - tr_tran
if x < 0 the
return tr_time
exit
else
next row of tr_hist
end if
end loop1
end loop2
return 0
end fucntion.
Kindly suggest regarding the issue ASAP
thanks in advance!!!!!!!!
