Differences
This shows you the differences between two versions of the page.
control_sql_-_query_to_breakdown_reconciliation_entries [2019/01/27 11:29] 127.0.0.1 external edit |
control_sql_-_query_to_breakdown_reconciliation_entries [2019/02/05 11:40] admin |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== | ||
- | |||
- | |||
- | |||
===== Explanation of SQL ===== | ===== Explanation of SQL ===== | ||
Line 22: | Line 18: | ||
<code sql> | <code sql> | ||
- | declare | + | DECLARE |
- | declare | + | DECLARE |
- | Set @GLAccountID = 90; | + | |
- | Set @StartDate = ' | + | SET @GLAccountID = 90; |
- | select | + | SET @StartDate = ' |
+ | |||
+ | SELECT | ||
( | ( | ||
- | select | + | SELECT |
- | | + | MONTH(StartDateTime) |
- | | + | MONTH(EndDateTime) |
- | | + | SummaryAmount |
- | | + | DetailAmount |
- | | + | SummaryAmount - DetailAmount |
- | | + | UseActualTime |
- | | + | LinkID |
- | | + | (SELECT |
- | cast(BillingNotes | + | CAST(BillingNotes |
- | from Journal | + | |
- | where ClassTypeID = 9750 and EndDateTime > @StartDate | + | |
- | and LinkID = @GLAccountID | + | |
) Rs | ) Rs | ||
- | full outer join | + | |
+ | FULL OUTER JOIN | ||
+ | |||
( | ( | ||
- | select | + | SELECT |
- | case when Amount > 0 then Amount | + | CASE WHEN Amount > 0 THEN Amount |
- | case when Amount < 0 then -Amount | + | CASE WHEN Amount < 0 THEN -Amount |
- | | + | Description, |
- | | + | (SELECT |
- | | + | SUM(CASE WHEN Amount > 0 THEN Amount |
- | | + | SUM(CASE WHEN Amount < 0 THEN -Amount |
- | | + | SUM(Amount) |
- | from GL | + | |
- | where ReconciliationID | + | |
- | and GLAccountID = @GLAccountID | + | |
- | ) GLs on Rs.ReconciliationID = GLs.ReconciliationID | + | ) GLs ON Rs.ReconciliationID = GLs.ReconciliationID |
- | order by Rs.ReconciliationID | + | ORDER BY Rs.ReconciliationID |
</ | </ | ||