Hi Experts,

I posted on other forums but cant still looking for suggestion to this problem.

I have here my script in view which makes Table A comes up. I want to display qty in Order column just once considering the nearest expiration date(edate) per dnum and the capacity to serve an item (onHand). ex. (dnum #593) If ordered qty is 10pcs, it should look for nearest expiration date which is 12/06/2014 then look at onhand if it can serve the qty. given that it has 50pcs onhand then the condition meet, so it will display 10 inline to that line item. I don't know how will i work on this. any suggestions will do.
Code:
SELECT t2.[dnum], t2.[ddate], 
   t2.[ccode], t2.[cname], 
   t1.[icode],t1.[bnum], 
   t1.[wcode],t1.[edate],t1.OnHand,
   t2.Order 
  FROM   (SELECT A.[dnum], A.[status],
           A.[ddate], A.[ccode], 
           A.[cname], B.[icode], 
           B.[desc], B.Order, 
           b.wcode 
  FROM  A INNER JOIN B ON A.dentry = B.dentry 
    WHERE status = 'O') t2 
    LEFT JOIN (SELECT T0.[icode], T0.[bnum], 
                     T0.[wcode], T0.[edate], T0.Onhand
              FROM   oibt T0 
              WHERE  T0.Onhand> 0 
            ) t1 
          ON t1.icode = t2.icode AND t1.wcode = t2.wcode
Table A (sample output upon executing code)
Code:
  DNUM    ddate    ccode   cname     icode      bnum       wcode    edate      OnHand      Order 
  593  3/26/2013    C01   CUST01    Item001  MLUF-20121224  W01    12/06/2014    50         10
  593  3/26/2013    C01   CUST01    Item001  MLUF-20130329  W01    03/28/2015    1640       10
  593  3/26/2013    C01   CUST01    Item001  MLUF-20121224  W01    12/23/2014    10         10
  593  3/26/2013    C01   CUST01    Item001  MGTJ-20130201  W01    02/01/20158   830        10
  866  09/04/2013   C45   CUST45    Item835  MFR-20120901   w01    09/01/2014    15          9
  866  09/04/2013   C45   CUST45    Item835  MPPS-20110121  w01    09/01/2014    6           9
  928  04/18/2013   C36   CUST36    Item221  MFC-201209011  w01    01/21/2013    1          10
  928  04/18/2013   C36   CUST36    Item221  MASW-20130201  w01    02/01/2015    37         10
Table B (How will i get this results??)
Code:
  DNUM    ddate    ccode   cname     icode      bnum       wcode    edate      OnHand      Order 
  593  3/26/2013    C01   CUST01    Item001  MLUF-20121224  W01    12/06/2014    50         10
  593  3/26/2013    C01   CUST01    Item001  MLUF-20130329  W01    03/28/2015    1640      
  593  3/26/2013    C01   CUST01    Item001  MLUF-20121224  W01    12/23/2014    10       
  593  3/26/2013  C01   CUST01    Item001  MGTJ-20130201  W01    02/01/2015    830        
  866  09/04/2013   C45   CUST45    Item835  MFR-20120901   w01    09/01/2014    15          9
  866  09/04/2013   C45   CUST45    Item835  MPPS-20110121  w01    09/01/2014    6         
  928  04/18/2013   C36   CUST36    Item221  MFC-201209011  w01    01/21/2013    1          1
  928  04/18/2013   C36   CUST36    Item221  MASW-20130201  w01    02/01/2015    37