Differences

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

Link to this comparison view

control_sql_-_balance_sheet_or_journal_out_of_balance [2019/01/27 11:28]
127.0.0.1 external edit
control_sql_-_balance_sheet_or_journal_out_of_balance [2019/02/05 11:20] (current)
admin
Line 1: Line 1:
-====== ​ ====== 
- 
- 
- 
 ===== Explanation of SQL ===== ===== Explanation of SQL =====
  
Line 23: Line 19:
 <code sql> <code sql>
 -- Find any journal entry that is our of balance by the financial summary (even if sums to zero) -- Find any journal entry that is our of balance by the financial summary (even if sums to zero)
-declare ​@StartDate datetime; +  
-declare ​@EndDate datetime; +DECLARE ​@StartDate datetime; 
-set @StartDate = '​12/​1/​2009';​ +DECLARE ​@EndDate datetime; 
-set @EndDate = '​12/​31/​2009 23:​59:​59';​ +  
-select Sum(AssetAmount) ​as AssetAmount,​ +SET @StartDate = '​12/​1/​2009';​ 
-       Sum(LiabilityAmount) ​as LiabilityAmount,​ +SET @EndDate = '​12/​31/​2009 23:​59:​59';​ 
-       Sum(EquityAmount) ​as EquityAmount,​ +  
-       Sum(PandLAmount) ​as PandLAmount,​ +SELECT SUM(AssetAmount) ​AS AssetAmount,​ 
-    (Sum(AssetAmount) + Sum(LiabilityAmount) + Sum(EquityAmount) + Sum(PandLAmount)) ​as Delta,+       SUM(LiabilityAmount) ​AS LiabilityAmount,​ 
 +       SUM(EquityAmount) ​AS EquityAmount,​ 
 +       SUM(PandLAmount) ​AS PandLAmount,​ 
 +       ​(SUM(AssetAmount) + SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount)) ​AS Delta,
        ​JournalID,​        ​JournalID,​
-       (select ​StartDateTime ​from Journal ​where ID = JournalID) ​as JournalDate,​ +       (SELECT ​StartDateTime ​FROM Journal ​WHERE ID = JournalID) ​AS JournalDate,​ 
-       ​NULL ​as TransactionID,​ +       ​NULL ​AS TransactionID,​ 
-    NULL as OrderNumber +       ​NULL AS OrderNumber 
-from+  
 +FROM
 ( (
-  ​select ​+  ​SELECT ​
      ​TransactionID,​      ​TransactionID,​
      ​JournalID,​      ​JournalID,​
-     (case when GLClassificationType ​between ​1000 and 1999 then '​Asset'​ +     (CASE WHEN GLClassificationType ​BETWEEN ​1000 AND 1999 THEN '​Asset'​ 
-           when GLClassificationType ​between ​2000 and 2999 then '​Liability'​ +           WHEN GLClassificationType ​BETWEEN ​2000 AND 2999 THEN '​Liability'​ 
-           when GLClassificationType ​between ​3000 and 3999 then '​EQuity'​ +           WHEN GLClassificationType ​BETWEEN ​3000 AND 3999 THEN '​EQuity'​ 
-      ​else '​P&​L'​ +      ​ELSE '​P&​L'​ 
-      ​end +      ​END 
-     ​) ​as AccountType,​ +     ​) ​AS AccountType,​ 
-     case when GLClassificationType ​between ​1000 and 1999 then Amount ​else end as AssetAmount,​ +     CASE WHEN GLClassificationType ​BETWEEN ​1000 AND 1999 THEN Amount ​ELSE END AS AssetAmount,​ 
-     case when GLClassificationType ​between ​2000 and 2999 then Amount ​else end as LiabilityAmount,​ +     CASE WHEN GLClassificationType ​BETWEEN ​2000 AND 2999 THEN Amount ​ELSE END AS LiabilityAmount,​ 
-     case when GLClassificationType ​between ​3000 and 3999 then Amount ​else end as EquityAmount,​ +     CASE WHEN GLClassificationType ​BETWEEN ​3000 AND 3999 THEN Amount ​ELSE END AS EquityAmount,​ 
-     case when GLClassificationType ​between ​4000 and 7999 then Amount ​else end as PandLAmount +     CASE WHEN GLClassificationType ​BETWEEN ​4000 AND 7999 THEN Amount ​ELSE END AS PandLAmount 
-  ​from GL +  ​FROM GL 
-  ​where EntryDateTime ​between ​@StartDate ​and @EndDate +  ​WHERE EntryDateTime ​BETWEEN ​@StartDate ​AND @EndDate 
-    ​and TransactionID ​is NULL +    ​AND TransactionID ​IS NULL 
-as BalanceSheet +AS BalanceSheet 
-group by JournalID, TransactionID +  
-having Sum(AssetAmount) ​ -(Sum(LiabilityAmount) + Sum(EquityAmount) + Sum(PandLAmount)) +GROUP BY JournalID, TransactionID 
-union all+HAVING SUM(AssetAmount) ​<> ​-(SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount)) 
 +  
 +  
 +UNION ALL 
 + 
 -- Find any order that is our of balance by the financial summary (even if sums to zero) -- Find any order that is our of balance by the financial summary (even if sums to zero)
-select Sum(AssetAmount) ​as AssetAmount,​ +  
-       Sum(LiabilityAmount) ​as LiabilityAmount,​ +SELECT SUM(AssetAmount) ​AS AssetAmount,​ 
-       Sum(EquityAmount) ​as EquityAmount,​ +       SUM(LiabilityAmount) ​AS LiabilityAmount,​ 
-       Sum(PandLAmount) ​as PandLAmount,​ +       SUM(EquityAmount) ​AS EquityAmount,​ 
-    (Sum(AssetAmount) + Sum(LiabilityAmount) + Sum(EquityAmount) + Sum(PandLAmount)) ​as Delta, +       SUM(PandLAmount) ​AS PandLAmount,​ 
-       ​NULL ​as JournalID,​ +       ​(SUM(AssetAmount) + SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount)) ​AS Delta, 
-    NULL as JournalDate,​+       ​NULL ​AS JournalID,​ 
 +       ​NULL AS JournalDate,​
        ​TransactionID,​        ​TransactionID,​
-       (select coalesce(OrderNumber,​ BillNumber) ​from TransHeader ​where ID = TransactionID) ​as TransHeaderNumber +       (SELECT COALESCE(OrderNumber,​ BillNumber) ​FROM TransHeader ​WHERE ID = TransactionID) ​AS TransHeaderNumber 
-from+FROM
 ( (
-  ​select ​+  ​SELECT ​
      ​TransactionID,​      ​TransactionID,​
      ​JournalID,​      ​JournalID,​
-     (case when GLClassificationType ​between ​1000 and 1999 then '​Asset'​ +     (CASE WHEN GLClassificationType ​BETWEEN ​1000 AND 1999 THEN '​Asset'​ 
-           when GLClassificationType ​between ​2000 and 2999 then '​Liability'​ +           WHEN GLClassificationType ​BETWEEN ​2000 AND 2999 THEN '​Liability'​ 
-           when GLClassificationType ​between ​3000 and 3999 then '​EQuity'​ +           WHEN GLClassificationType ​BETWEEN ​3000 AND 3999 THEN '​EQuity'​ 
-      ​else '​P&​L'​ +      ​ELSE '​P&​L'​ 
-      ​end +      ​END 
-     ​) ​as AccountType,​ +     ​) ​AS AccountType,​ 
-     case when GLClassificationType ​between ​1000 and 1999 then Amount ​else end as AssetAmount,​ +     CASE WHEN GLClassificationType ​BETWEEN ​1000 AND 1999 THEN Amount ​ELSE END AS AssetAmount,​ 
-     case when GLClassificationType ​between ​2000 and 2999 then Amount ​else end as LiabilityAmount,​ +     CASE WHEN GLClassificationType ​BETWEEN ​2000 AND 2999 THEN Amount ​ELSE END AS LiabilityAmount,​ 
-     case when GLClassificationType ​between ​3000 and 3999 then Amount ​else end as EquityAmount,​ +     CASE WHEN GLClassificationType ​BETWEEN ​3000 AND 3999 THEN Amount ​ELSE END AS EquityAmount,​ 
-     case when GLClassificationType ​between ​4000 and 7999 then Amount ​else end as PandLAmount +     CASE WHEN GLClassificationType ​BETWEEN ​4000 AND 7999 THEN Amount ​ELSE END AS PandLAmount 
-  ​from GL +  ​FROM GL 
-  ​where EntryDateTime ​between ​@StartDate ​and @EndDate +  ​WHERE EntryDateTime ​BETWEEN ​@StartDate ​AND @EndDate 
-        ​and TransactionID IS NOT NULL +        ​AND TransactionID IS NOT NULL 
-as BalanceSheet +AS BalanceSheet 
-group by TransactionID,​ JournalID +  
-having Sum(AssetAmount) ​ -(Sum(LiabilityAmount) + Sum(EquityAmount) + Sum(PandLAmount))+GROUP BY TransactionID,​ JournalID 
 +HAVING SUM(AssetAmount) ​<> ​-(SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount))
 </​code>​ </​code>​