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. | 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+ | ||
- | |||
- | |||