Differences

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

Link to this comparison view

change_last_statement_date_in_reconcile_bank_statement [2019/01/30 09:22] (current)
Line 1: Line 1:
 +===== Explanation of SQL =====
 +
 +
 +
 +This query will locate the last bank reconciliation that was posted within Control, it will then adjust the dates of that record to modify the last statement date as seen in the screen below.
 +
 +
 +
 +{{::reconcile_bank_statement_-_last_statement_date.png?nolink&|}}
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**High**. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL **are permanent and non-reversable**.
 +
 +
 +
 +===== SQL To List All Reconciliations =====
 +
 +
 +
 +<code sql>
 +select
 +AccountName as BankingAccount
 +, J.ID as ReconciliationID
 +, JournalActivityText as Type
 +, SummaryAmount as [Starting Balance]
 +, DetailAmount as [Ending Balance]
 +, StartDateTime
 +, EndDateTime
 +, (SELECT Count(*) FROM Ledger GL where GL.ReconciliationID = J.ID) as [Reconciled Cnt]
 +, (SELECT SUM(Amount) FROM Ledger GL where GL.ReconciliationID = J.ID) as [Reconciled Amt]
 +, SummaryAmount
 +    + (SELECT SUM(Amount) FROM Ledger GL where GL.ReconciliationID = J.ID)
 +    - DetailAmount as [Reconciled Delta]
 +from Journal J
 +Left Join GLAccount GLA on GLA.ID = J.LinkID and GLA.ClassTypeID = 8001
 +where J.ClassTypeID = 9750
 +and J.StartDateTime > '1/1/2013'
 +order by AccountName, J.ID desc
 +</code>
 +
 +
 +
 +===== SQL To Modify Last Statement Date =====
 +
 +
 +
 +<code sql>
 +-- Modify the @LastStatementDate to be the actual date you want the last statement date to be.
 +-- After executing this statement you should close and re-open Control to refresh the changes.
 +DECLARE @LastStatementDate DateTime;
 +SET @LastStatementDate = '2014-07-08 00:00:00.000';
 +UPDATE Journal
 +SET EndDateTime = @LastStatementDate
 +, TotalTime = (@LastStatementDate - StartDateTime - 2)
 +-- We use the - 2 because Delphi and SQL store total times differently, the -2 is needed to ensure that Delphi recognizes the proper date.
 +, CompletedDateTime = @LastStatementDate
 +, QueryEndDateTime = @LastStatementDate
 +, SeqID = SeqID + 1
 +WHERE ID = (SELECT TOP 1 ID FROM Journal WHERE ClassTypeID = 9750 ORDER BY EndDateTime DESC)
 +;
 +UPDATE Ledger
 +SET ReconciliationDateTime = @LastStatementDate
 +, SeqID = SeqID + 1
 +WHERE ReconciliationID = (SELECT TOP 1 ID FROM Journal WHERE ClassTypeID = 9750 ORDER BY EndDateTime DESC)
 +;
 +</code>
 +
 +
 +
 +If you have any issues with the last statement date not actually appearing as the very last enddatetime in the journal it could be related to an issue with the completed date time. Running the SQL below will resolve that issue.
 +
 +
 +
 +<code sql>
 +update journal set completeddatetime = enddatetime, seqid = seqid + 1 where classtypeid = 9750
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 7/8/2014 by Brandon Readlinger
 +
 +
 +