This SQL produces a Check Register for a given payment (GL) account and time period.

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

DECLARE @GLAccountID INT = 10118
DECLARE @StartDate datetime = '10/1/2014';
DECLARE @EndDate datetime = '11/1/2014';
-- two cases to consider:
--   Deposits and Journal -> Just pull the journal
--     Bill Payments -> Need to consolidate entries that are in the same SummaryJournal
-- Step 1. Build a Table so we can determine the correct journal and also update the Offset Account
DECLARE @T TABLE (
   ID INT PRIMARY KEY,
   JournalType VARCHAR(32),
   JournalActivityType INT,
   EntryDateTime datetime,
   DisplayNumber VARCHAR(64),
   CompanyName VARCHAR(64),
   Amount FLOAT,
   BillNumber INT,
   VoidedDateTime datetime,
   ReconciledDateTime datetime,
   DetailedJournalID INT,
   DetailedJournalClassTypeID INT,
   SummaryJournalID INT
    );
-- Step 2. Populate the Table
INSERT INTO @T
SELECT
    GL.ID AS GLID
    , COALESCE(Detail.JournalActivityText, 'Invalid Data') AS JournalType
    , Detail.JournalActivityType AS JournalActivityType
    , GL.EntryDateTime
    , Payment.DisplayNumber AS DisplayNumber
    , (SELECT CompanyName FROM Account A WHERE A.ID = GL.AccountID) AS CompanyName
    , GL.Amount
    , (SELECT BillNumber FROM TransHeader WHERE ID = GL.TransactionID) AS BillNumber
    , Detail.VoidedDateTime AS VoidedDateTime
    , GL.ReconciliationDateTime
    , GL.JournalID AS DetailedJournalID
    , GL.JournalClassTypeID AS DetailedJournalClassTypeID
    , Detail.SummaryID AS SummaryJournalID
FROM GL
LEFT JOIN Journal Detail  ON Detail.ID  = GL.JournalID
LEFT JOIN Payment ON Payment.ID = Detail.SummaryID
WHERE GLAccountID = @GLAccountID
AND EntryDateTime BETWEEN @StartDate AND @EndDate
-- Step 3. For payments, re-consolidate the answer based on the SummaryJournalID record
SELECT
    MAX(ID) AS GLID
    , MAX(JournalType) AS JournalType
    , MAX(JournalActivityType) AS JournalActivityType
    , MAX(EntryDateTime) AS EntryDateTime
    , MAX(DisplayNumber) AS DisplayNumber
    , MAX(CompanyName) AS CompanyName
    , SUM(Amount) AS Amount
    , MAX(BillNumber) AS BillNumber
    , MAX(VoidedDateTime) AS VoidedDateTime
    , MAX(ReconciledDateTime) AS ReconciledDateTime
    , MAX(DetailedJournalID) AS DetailedJournalID
    , MAX(DetailedJournalClassTypeID) AS DetailedJournalClassTypeID
    , SummaryJournalID
FROM @T
GROUP BY SummaryJournalID
ORDER BY EntryDateTime

• Entered : 10/2014
• Version : 4.6+