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.

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.

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

-- 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)
;

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.

UPDATE journal SET completeddatetime = enddatetime, seqid = seqid + 1 WHERE classtypeid = 9750

• Entered : 7/8/2014 by Brandon Readlinger