Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   How to display a value only once in sql when conditions met (http://www.go4expert.com/forums/display-value-sql-conditions-met-t29639/)

User001 25Apr2013 07:48

How to display a value only once in sql when conditions met
 
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



All times are GMT +5.5. The time now is 12:53.