Here is the original query with the time stamp
Select a.app_key AS APP_NUMBER, a.CREDIT_AMOUNT, REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM, c.cts_contract_number AS APP_CONTRACT_NUMBER,
CASE co.CO_UD_APP_STATUS_TBDESC WHEN 'PO Issued' THEN CASE a.APP_DECISION_CODE WHEN 7 THEN 'Booked' ELSE 'PO Issued' END WHEN 'Approved' THEN
CASE co.CO_LESSOR WHEN '421' THEN 'Approved - SFP' WHEN '423' THEN 'Approved - SFP' WHEN '424' THEN 'Approved - SFP' ELSE 'Approved' END ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS, CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER, CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8 THEN CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)') ELSE NULL END AS CCAN, CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED, CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)')
AS FEDERAL_ID
FROM dbo.APPLICATION AS a LEFT OUTER JOIN
dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY LEFT OUTER JOIN
dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY LEFT OUTER JOIN
dbo.Contract_Setup AS c on a.app_key = c.cts_app_fkey
WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) and a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) or a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())
Here is a sample result set
APP_NUMBERCREDIT_AMOUNTDEALER_NUMAPP_CONTRACT_NUMBERAPPLICATION_STATUSBOOKING_DATECUSTOMERCUSTOMER_DBACCANDATE_ENTEREDFEDERAL_ID
9553152281.007183385800NULLNULLNULLZirh Limited Liability CompanyNULL134751804/25/2013NULL
9551926850.009549701691NULLApprovedNULLClaremore Tire Center, Inc.NULL138083104/25/2013NULL
95522920000.002677597066NULLApproved - SFPNULLSteven S Levine DMDNULL138085404/25/2013NULL
95300846000.006196961699NULLApprovedNULLTom Paige Catering CompanyNULL134497904/12/2013NULL
95525217601.008008168138NULLApprovedNULLAmerican Tire & Auto Care, IncNULL138087104/25/201372-0944075
Here is the query with no time stamp where I get no results.
Select a.app_key AS APP_NUMBER, a.CREDIT_AMOUNT, REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM, c.cts_contract_number AS APP_CONTRACT_NUMBER,
CASE co.CO_UD_APP_STATUS_TBDESC WHEN 'PO Issued' THEN CASE a.APP_DECISION_CODE WHEN 7 THEN 'Booked' ELSE 'PO Issued' END WHEN 'Approved' THEN
CASE co.CO_LESSOR WHEN '421' THEN 'Approved - SFP' WHEN '423' THEN 'Approved - SFP' WHEN '424' THEN 'Approved - SFP' ELSE 'Approved' END ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS, CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER, CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8 THEN CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)') ELSE NULL END AS CCAN, CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED, CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)')
AS FEDERAL_ID
FROM dbo.APPLICATION AS a LEFT OUTER JOIN
dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY LEFT OUTER JOIN
dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY LEFT OUTER JOIN
dbo.Contract_Setup AS c on a.app_key = c.cts_app_fkey WHERE c.CTS_LAST_SAVED_DATE = CAST(DATEADD(day,1,CURRENT_TIMESTAMP) as DATE) and a.APP_LAST_SAVED_DATE = CAST(DATEADD(day,1,CURRENT_TIMESTAMP) as DATE) or c.CTS_LAST_SAVED_DATE = CAST(CURRENT_TIMESTAMP as DATE)