Differences

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

@@ 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+