August 8, 2012 at 8:42 am
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]
August 8, 2012 at 9:31 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply