combine query

• coool_sweet

  SSCommitted

  Points: 1892

  More actions

  July 28, 2017 at 4:07 pm

  #322910

  ok below Is my query

   SELECT  
    LN.PRODUCT_ID ,
    ITEM_STATUS_NAME.TXT approve_st,
    ITEM_HISTORY.DATE approve_dt
  FROM
    LN,
    ITEM,
    ITEM_STATUS_NAME,
    ITEM_HISTORY
  WHERE
    ( ITEM.APP_NO=ITEM_HISTORY.APP_NO  )
    AND  ( ITEM.ITEM_NUM=ITEM_HISTORY.ITEM_NUM  )
    AND  ( ITEM_HISTORY.OCA=ITEM_STATUS_NAME.ITEM_NUM  )
    AND  ( LN.APP_NO=ITEM.APP_NO  )
    AND  (
    LN.PRODUCT_ID  =  '99'
    AND  ITEM.DESC  =  'approve'
    )

  the same query I m using 3-4 times

  SELECT  
    LN.PRODUCT_ID ,
    ITEM_STATUS_NAME.TXT final_st,
    ITEM_HISTORY.DATE final_dt
  FROM
    LN,
    ITEM,
    ITEM_STATUS_NAME,
    ITEM_HISTORY
  WHERE
    ( ITEM.APP_NO=ITEM_HISTORY.APP_NO  )
    AND  ( ITEM.ITEM_NUM=ITEM_HISTORY.ITEM_NUM  )
    AND  ( ITEM_HISTORY.OCA=ITEM_STATUS_NAME.ITEM_NUM  )
    AND  ( LN.APP_NO=ITEM.APP_NO  )
    AND  (
    LN.PRODUCT_ID  =  '99'
    AND  ITEM.DESC  =  'Final'
    )

  SELECT  
    LN.PRODUCT_ID ,
    ITEM_STATUS_NAME.TXT submit_st,
    ITEM_HISTORY.DATE submit_dt
  FROM
    LN,
    ITEM,
    ITEM_STATUS_NAME,
    ITEM_HISTORY
  WHERE
    ( ITEM.APP_NO=ITEM_HISTORY.APP_NO  )
    AND  ( ITEM.ITEM_NUM=ITEM_HISTORY.ITEM_NUM  )
    AND  ( ITEM_HISTORY.OCA=ITEM_STATUS_NAME.ITEM_NUM  )
    AND  ( LN.APP_NO=ITEM.APP_NO  )
    AND  (
    LN.PRODUCT_ID  =  '99'
    AND  ITEM.DESC  =  'submit'
    )

  what I want is

  select LN.PRODUCT_ID ,
    ITEM_STATUS_NAME.TXT approve_st,
    ITEM_HISTORY.DATE approve_dt,

  ITEM_STATUS_NAME.TXT final_st,
    ITEM_HISTORY.DATE final_dt,

  ITEM_STATUS_NAME.TXT submit_st,
    ITEM_HISTORY.DATE submit_dt

  from

  combine every 4 filed in 1 query , here there is more that one product_id, I just show you for one only.

  result

  productid             final_st                   final_dt                  submit_st            submit_dt          approve_st          approve_dt

  please help.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 29, 2017 at 3:59 am

  #1953275

  without seeing sample data ...here;s a best guess sample idea

  CREATE TABLE #yourdata(
 PID INT
,tdate DATETIME
,tdesc VARCHAR(8)
);
INSERT INTO #yourdata(PID,tdate,tdesc) VALUES
(1,'20170101','fin'),(2,'20170103','fin'),(3,'20170105','fin')
,(1,'20170106','sub'),(2,'20170108','sub'),(3,'20170110','sub')
,(1,'20170111','app'),(2,'20170113','app'),(3,'20170115','app');

  select PID,
MAX(CASE WHEN tdesc = 'fin' THEN tdate ELSE NULL END) as findate,
MAX(CASE WHEN tdesc = 'sub' THEN tdate ELSE NULL END) as subdate,
MAX(CASE WHEN tdesc = 'app' THEN tdate ELSE NULL END) as appdate
FROM #yourdata
GROUP BY PID

  DROP TABLE #yourdata

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day