======  ======



===== Explanation of SQL =====



If Control is terminated (e.g., CTRL-ALT-DEL) while processing an online credit card, the results of the credit card being run may not be written to the database.  However, the C3S running as a separate service will still have a record of these.



This query searches for Credit Card Processed activities that do not have a completion event, and then looks up the status of those from the C3S tables.  From there, the fix must still be applied manually (usually by giving the customer a manual credit), but this query will identify them.



The date and time of the range to be queries must be adjusted before running.



===== Risk of Data Corruption if Run Improperly =====

**None**. This is a selection query and no data is modified in the running of it.



===== SQL =====



<code sql>
-- This query identifies payments that 
-- were processed but a response was not
-- logged in Control.
select 
    Journal.StartDateTime as [Date and Time], 
    Journal.ModifiedByUser as [User], SummaryAmount as Amount,
    Journal.Description, 
    CT.Amount as CCAmount, WasSuccessful, AuthorizationCode,
    Account.CompanyName, Account.AccountNumber as CompanyNumber,
    Journal.ID
    --, Journal.Notes
    --, CT.*
from CCCSTransactions CT
join CCCSCustomer CC on CT.CustomerGUID = CC.CustomerGUID 
join Journal on CC.CustomerID = Journal.AccountID
left join Account on Journal.AccountID = Account.ID
where 
Journal.ClassTypeID = 20030
and Journal.Notes not like '%success%' 
and Journal.Notes not like '%failed%' 
and CT.ProcessedDateTime between '11/1/2010' and '2/1/2011'
and Journal.StartDateTime between '11/1/2010' and '2/1/2011'
and abs(DATEDIFF(minute, CT.ProcessedDateTime, Journal.StartDateTime)) < 10
order by Journal.ID desc
</code>



===== Version Information =====
  * Entered : 01/2011
  * Version : 3.4+