0 Replies Latest reply: Jun 20, 2011 7:30 AM by kushal_gulwani RSS

    How to compare data between certain tables

      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!!!!!!!!