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 0 end as AssetAmount, +     CASE WHEN GLClassificationType BETWEEN 1000 AND 1999 THEN Amount ELSE 0 END AS AssetAmount, 
-     case when GLClassificationType between 2000 and 2999 then Amount else 0 end as LiabilityAmount, +     CASE WHEN GLClassificationType BETWEEN 2000 AND 2999 THEN Amount ELSE 0 END AS LiabilityAmount, 
-     case when GLClassificationType between 3000 and 3999 then Amount else 0 end as EquityAmount, +     CASE WHEN GLClassificationType BETWEEN 3000 AND 3999 THEN Amount ELSE 0 END AS EquityAmount, 
-     case when GLClassificationType between 4000 and 7999 then Amount else 0 end as PandLAmount +     CASE WHEN GLClassificationType BETWEEN 4000 AND 7999 THEN Amount ELSE 0 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 0 end as AssetAmount, +     CASE WHEN GLClassificationType BETWEEN 1000 AND 1999 THEN Amount ELSE 0 END AS AssetAmount, 
-     case when GLClassificationType between 2000 and 2999 then Amount else 0 end as LiabilityAmount, +     CASE WHEN GLClassificationType BETWEEN 2000 AND 2999 THEN Amount ELSE 0 END AS LiabilityAmount, 
-     case when GLClassificationType between 3000 and 3999 then Amount else 0 end as EquityAmount, +     CASE WHEN GLClassificationType BETWEEN 3000 AND 3999 THEN Amount ELSE 0 END AS EquityAmount, 
-     case when GLClassificationType between 4000 and 7999 then Amount else 0 end as PandLAmount +     CASE WHEN GLClassificationType BETWEEN 4000 AND 7999 THEN Amount ELSE 0 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>