Trouble with Left Join to Subquery

• stphnlwlsh

  SSC Veteran

  Points: 283

  More actions

  August 8, 2012 at 8:42 am

  #260567

  I have a table that stores comment codes for a financial institution. When a request is made, a date and timestamped code is stored on the table. Additionally when the request is fulfilled a date and timestamped code is store on the table. I'm attempting to get the non-fulfilled requests from the previous day to determine the carry over from the query below, but it doesn't seem to be working.

  I have ranges for the dates because the request could be made on a given day, but has a 2 day turn time SLA.

  Any suggestions?

  SELECT t.LOAN_NBR_NUM, t.SEQ_NBR, t.ENTERED_BY, t.CATEGORY, CONVERT(DATE, t.ENTER_DTS) AS REQUEST_DATE, t.ENTER_DTS AS PAYOFF_REQUEST_DTS, --sQ.ENTER_DTS AS FULFILLMENT_DTS,

  'Internal Beginning Carry Over' AS [Status]

  FROM #tmp t

  LEFT JOIN (SELECT t.LOAN_NBR_NUM, t.SEQ_NBR, t.ENTERED_BY, t.ENTER_DTS, t.CATEGORY

  FROM #tmp t

  WHERE CATEGORY IN ('Request Complete', 'Rejected') AND (

  CONVERT(DATE, t.ENTER_DTS) >= DATEADD(D, -3, CONVERT(DATE, CURRENT_TIMESTAMP)) AND

  CONVERT(DATE, t.ENTER_DTS) <= DATEADD(D, -1, CONVERT(DATE, CURRENT_TIMESTAMP)))) sQ

  ON t.LOAN_NBR_NUM = sQ.LOAN_NBR_NUM

  WHERE t.CATEGORY = 'Internal Request' AND

  CONVERT(DATE, t.ENTER_DTS) = DATEADD(D, -3, CONVERT(DATE, CURRENT_TIMESTAMP))-- AND

  sQ.ENTER_DTS IS NULL

  ORDER BY LOAN_NBR_NUM, SEQ_NBR

  [font="Courier New"]_______________________________
  Stephen Walsh[/font]

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  August 8, 2012 at 9:31 am

  #1523203

  We would need table scripts, sample data and expected results to give a sensible answer. See the first article linked in my signature line if you're in doubt.

  Without any of the above, all I can offer is a shot in the dark:

  SELECT t.LOAN_NBR_NUM

  ,t.SEQ_NBR

  ,t.ENTERED_BY

  ,t.CATEGORY

  ,CONVERT(DATE, t.ENTER_DTS) AS REQUEST_DATE

  ,t.ENTER_DTS AS PAYOFF_REQUEST_DTS

  ,'Internal Beginning Carry Over' AS [Status]

  FROM #tmp t

  WHERE NOT EXISTS (

  SELECT *

  FROM #tmp t1

  WHERE CATEGORY IN ('Request Complete','Rejected')

  AND CONVERT(DATE, t.ENTER_DTS) BETWEEN DATEADD(D, - 3, CONVERT(DATE, CURRENT_TIMESTAMP))

  AND DATEADD(D, - 1, CONVERT(DATE, CURRENT_TIMESTAMP))

  AND t.LOAN_NBR_NUM = t1.LOAN_NBR_NUM

  )

  AND t.CATEGORY = 'Internal Request'

  AND CONVERT(DATE, t.ENTER_DTS) = DATEADD(D, - 3, CONVERT(DATE, CURRENT_TIMESTAMP))

  AND sQ.ENTER_DTS IS NULL

  ORDER BY LOAN_NBR_NUM

  ,SEQ_NBR

  -- Gianluca Sartori