solve for the customers last entry in a table and then see if it meets criteria

• twdavis-893252

  Ten Centuries

  Points: 1195

  More actions

  January 16, 2009 at 9:47 am

  #71223

  I need to solve for the customers last entry in a table and then see if it meets criteria

  I am trying to find all customers that credit was approved 28 days ago or more. The table( PS_ORD_HOLD_AUDIT B) that has the credit information and looks like example 1

  My trouble is the PS_ORD_HOLD_AUDIT B(example1) has duplicate information in it where only the date is different and I only want customers whos last entry in the table REASON_CD=CRD_APPR

  sql statement:

  SELECT A.SOLD_TO_CUST_ID, C.NAME1, DATEDIFF(DAY, (CONVERT(CHAR(10),B.STATUS_DT,121)),GETDATE()) AS DAYS,

  A.PYMNT_TERMS_CD, A.ORDER_NO,C.SETID,C.CUST_ID, B.REASON_CD

  FROM PS_ORD_HEADER A, PS_ORD_HOLD_AUDIT B, PS_CUSTOMER C

  WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT

  AND A.ORDER_NO = B.ORDER_NO

  AND A.SOLD_TO_CUST_ID = C.CUST_ID

  AND ( A.PYMNT_TERMS_CD LIKE 'CIT%'

  OR A.PYMNT_TERMS_CD LIKE 'BB%')

  AND B.REASON_CD = 'CRD_APPR'

  AND DATEDIFF(DAY, B.STATUS_DT,GETDATE()) > '28'

  AND A.ORDER_STATUS = 'O'

  and EFF_STATUS='I' AND B.REASON_CD = 'CRD_APPR'

  it returns information like example 2:

• twdavis-893252

  Ten Centuries

  Points: 1195

  More actions

  January 16, 2009 at 2:20 pm

  #928529

  I fixed it by adding

  AND B.ACTIVE_HOLD_DTTM= (SELECT MAX(MX.ACTIVE_HOLD_DTTM) FROM PS_ORD_HOLD_AUDIT MX

  WHERE B.ORDER_NO=MX.ORDER_NO

  ND MX.ACTIVE_HOLD_DTTM <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))

  to the where statement