Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_identify_credit_cards_processed_but_not_saved [2019/01/27 11:28] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== 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+
 +
 +
 +