CDP Teller: Shows the transactions & ID's that haven't been confirmed and need to be re-sent to DMS

SELECT        trans_date AS [Transaction Date], DateSend, LocalID, CASE WHEN (remoteid IS NULL) THEN 'Teller Transaction Not Confirmed, Should Resend This' END AS [Status]
FROM            trans_head
WHERE        datesend IS NOT NULL AND RemoteID IS NULL AND DateConfirm IS NULL AND cast(datesend AS date) < cast(getdate() - 2 AS date)
UNION
SELECT        [LastUpdated] AS [Transaction Date], [DateSend], [ID] AS [LocalID], CASE WHEN (remoteid IS NULL) THEN 'Member ID Not Confirmed, Should Resend This - New Way of Scanning' END AS [Status]
FROM            [dbo].[MemberIdImage]
WHERE        datesend IS NOT NULL AND RemoteID IS NULL AND DateConfirm IS NULL AND cast(datesend AS date) < cast(getdate() - 2 AS date)
UNION
SELECT        LastUpdated AS [Transaction Date], DateSend, ID AS [LocalID], CASE WHEN (remoteid IS NULL) THEN 'Member ID Not Confirmed, Should Resend This - Old Way of Scanning' END AS [Status]
FROM            Member_ID
WHERE        datesend IS NOT NULL AND RemoteID IS NULL AND DateConfirm IS NULL AND cast(datesend AS date) < cast(getdate() - 2 AS date)