Differences

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

Link to this comparison view

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. This SQL produces a Check Register for a given payment (GL) account and time period.
- 
- 
  
 ===== Risk of Data Corruption if Run Improperly ===== ===== Risk of Data Corruption if Run Improperly =====
  
 **None**. This is a selection query and no data is modified in the running of it. **None**. This is a selection query and no data is modified in the running of it.
- 
- 
- 
 ===== SQL ===== ===== SQL =====
- 
- 
  
 <code 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: -- two cases to consider:
 --   ​Deposits and Journal -> Just pull the journal --   ​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 -- Step 1. Build a Table so we can determine the correct journal and also update the Offset Account
 DECLARE @T TABLE ( DECLARE @T TABLE (
-   ​ID ​int PRIMARY KEY, +   ​ID ​INT PRIMARY KEY, 
-   ​JournalType ​varchar(32), +   ​JournalType ​VARCHAR(32), 
-   ​JournalActivityType ​int,+   ​JournalActivityType ​INT,
    ​EntryDateTime datetime,    ​EntryDateTime datetime,
-   ​DisplayNumber ​varchar(64), +   ​DisplayNumber ​VARCHAR(64), 
-   ​CompanyName ​varchar(64), +   ​CompanyName ​VARCHAR(64), 
-   ​Amount ​float+   ​Amount ​FLOAT
-   ​BillNumber ​int,+   ​BillNumber ​INT,
    ​VoidedDateTime datetime,    ​VoidedDateTime datetime,
    ​ReconciledDateTime datetime,    ​ReconciledDateTime datetime,
-   ​DetailedJournalID ​int+   ​DetailedJournalID ​INT
-   ​DetailedJournalClassTypeID ​int+   ​DetailedJournalClassTypeID ​INT
-   ​SummaryJournalID ​int+   ​SummaryJournalID ​INT
     );     );
 -- Step 2. Populate the Table -- 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 -- 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>​ </​code>​
  
 +===== Version Information =====
  
- 
-===== Version Information ===== 
   * Entered : 10/2014   * Entered : 10/2014
   * Version : 4.6+   * Version : 4.6+
- 
-