Differences

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

--- bank_register_report_sql [2019/01/30 09:22]
127.0.0.1 external edit
+++ bank_register_report_sql [2019/01/30 16:03] (current)
kcifreo
@@ Line 1: / Line 1: @@
-=Explanation of SQL=
+===== Explanation of SQL =====
 This SQL produces a Check Register for a given payment (GL) account and time period.
 ===== 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>
-declare @GLAccountID int = 10118
+DECLARE @GLAccountID INT = 10118
-declare @StartDate datetime = '10/1/2014';
+DECLARE @StartDate datetime = '10/1/2014';
-declare @EndDate datetime = '11/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
+--     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,
+   ID INT PRIMARY KEY,
-   JournalType varchar(32),
+   JournalType VARCHAR(32),
-   JournalActivityType int,
+   JournalActivityType INT,
    EntryDateTime datetime,
-   DisplayNumber varchar(64),
+   DisplayNumber VARCHAR(64),
-   CompanyName varchar(64),
+   CompanyName VARCHAR(64),
-   Amount float,
+   Amount FLOAT,
-   BillNumber int,
+   BillNumber INT,
    VoidedDateTime datetime,
    ReconciledDateTime datetime,
-   DetailedJournalID int,
+   DetailedJournalID INT,
-   DetailedJournalClassTypeID int,
+   DetailedJournalClassTypeID INT,
-   SummaryJournalID int
+   SummaryJournalID INT
     );
 -- Step 2. Populate the Table
-insert into @T
+INSERT INTO @T
-select
+SELECT
-	GL.ID as GLID
+    GL.ID AS GLID
-	, coalesce(Detail.JournalActivityText, 'Invalid Data') as JournalType
+    , COALESCE(Detail.JournalActivityText, 'Invalid Data') AS JournalType
-	, Detail.JournalActivityType as JournalActivityType
+    , Detail.JournalActivityType AS JournalActivityType
-	, GL.EntryDateTime
+    , GL.EntryDateTime
-	, Payment.DisplayNumber as DisplayNumber
+    , Payment.DisplayNumber AS DisplayNumber
-	, (Select CompanyName from Account A where A.ID = GL.AccountID) as CompanyName
+    , (SELECT CompanyName FROM Account A WHERE A.ID = GL.AccountID) AS CompanyName
-	, GL.Amount
+    , GL.Amount
-	, (select BillNumber from TransHeader where ID = GL.TransactionID) as BillNumber
+    , (SELECT BillNumber FROM TransHeader WHERE ID = GL.TransactionID) AS BillNumber
-	, Detail.VoidedDateTime as VoidedDateTime
+    , Detail.VoidedDateTime AS VoidedDateTime
-	, GL.ReconciliationDateTime
+    , GL.ReconciliationDateTime
-	, GL.JournalID as DetailedJournalID
+    , GL.JournalID AS DetailedJournalID
-	, GL.JournalClassTypeID as DetailedJournalClassTypeID
+    , GL.JournalClassTypeID AS DetailedJournalClassTypeID
-	, Detail.SummaryID as SummaryJournalID
+    , Detail.SummaryID AS SummaryJournalID
-from GL
+FROM GL
-left join Journal Detail  on Detail.ID  = GL.JournalID
+LEFT JOIN Journal Detail  ON Detail.ID  = GL.JournalID
-left join Payment on Payment.ID = Detail.SummaryID
+LEFT JOIN Payment ON Payment.ID = Detail.SummaryID
-where GLAccountID = @GLAccountID
+WHERE GLAccountID = @GLAccountID
-and EntryDateTime between @StartDate and @EndDate
+AND EntryDateTime BETWEEN @StartDate AND @EndDate
 -- Step 3. For payments, re-consolidate the answer based on the SummaryJournalID record
-select
+SELECT
-	max(ID) as GLID
+    MAX(ID) AS GLID
-	, max(JournalType) as JournalType
+    , MAX(JournalType) AS JournalType
-	, max(JournalActivityType) as JournalActivityType
+    , MAX(JournalActivityType) AS JournalActivityType
-	, max(EntryDateTime) as EntryDateTime
+    , MAX(EntryDateTime) AS EntryDateTime
-	, max(DisplayNumber) as DisplayNumber
+    , MAX(DisplayNumber) AS DisplayNumber
-	, max(CompanyName) as CompanyName
+    , MAX(CompanyName) AS CompanyName
-	, sum(Amount) as Amount
+    , SUM(Amount) AS Amount
-	, max(BillNumber) as BillNumber
+    , MAX(BillNumber) AS BillNumber
-	, max(VoidedDateTime) as VoidedDateTime
+    , MAX(VoidedDateTime) AS VoidedDateTime
-	, max(ReconciledDateTime) as ReconciledDateTime
+    , MAX(ReconciledDateTime) AS ReconciledDateTime
-	, max(DetailedJournalID) as DetailedJournalID
+    , MAX(DetailedJournalID) AS DetailedJournalID
-	, max(DetailedJournalClassTypeID) as DetailedJournalClassTypeID
+    , MAX(DetailedJournalClassTypeID) AS DetailedJournalClassTypeID
-	, SummaryJournalID
+    , SummaryJournalID
-from @T
+FROM @T
-group by SummaryJournalID
+GROUP BY SummaryJournalID
-order by EntryDateTime
+ORDER BY EntryDateTime
 </code>
+===== Version Information =====
-===== Version Information =====
   * Entered : 10/2014
   * Version : 4.6+