How to display a value only once in sql when conditions met

Discussion in 'MySQL' started by User001, Apr 25, 2013.

  1. User001

    User001 New Member

    Joined:
    Apr 25, 2013
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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 
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice