Historical profit and loss

From MediaWiki

(Difference between revisions)
Jump to: navigation, search
(Added hierarchical roll-ups)
Line 1: Line 1:
{{more statistics for|{{CURRENTYEAR}}|profit and loss}}{{note|This report is in development, and data shown on this page may be missing, poorly organized, inaccurate, or flat-out wrong.}}
{{more statistics for|{{CURRENTYEAR}}|profit and loss}}{{note|This report is in development, and data shown on this page may be missing, poorly organized, inaccurate, or flat-out wrong.}}
{{#tag:mysql|
{{#tag:mysql|
-
WITH CTE AS (
+
ITH RECURSIVE xfers AS (
-
   SELECT Source AS account, 'Debits' AS type, -Amount AS Amount
+
   SELECT Source AS account,
 +
        0 AS TYPE,
 +
        -Amount AS Amount
   FROM sa_general_journal
   FROM sa_general_journal
   UNION ALL  
   UNION ALL  
-
   SELECT Destination, 'Credits', Amount
+
   SELECT Destination,
-
   FROM sa_general_journal
+
        1,
 +
        Amount
 +
   FROM sa_general_journal gj
 +
),
 +
dbcr AS (
 +
  SELECT
 +
      acc.ID `Account`,
 +
      acc.Super,
 +
      acc.Name,
 +
      COALESCE(SUM(CASE WHEN x.type = 0  THEN Amount END), 0) AS Debits,
 +
      COALESCE(SUM(CASE WHEN x.type = 1 THEN Amount END), 0) AS Credits,
 +
      COALESCE(SUM(Amount), 0) AS Net
 +
  FROM sa_accounts acc
 +
  LEFT JOIN xfers x ON x.account = acc.ID
 +
--  WHERE acc.Report = "BS"
 +
--  WHERE acc.Report = "PL"
 +
  GROUP BY acc.ID
 +
),
 +
summary AS (
 +
  SELECT *
 +
  FROM dbcr
 +
  WHERE Net != 0
 +
  UNION ALL
 +
  SELECT d.Account, d.Super, d.Name, s.Debits, s.Credits, s.Net
 +
  FROM dbcr d
 +
  JOIN summary s ON d.Account = s.Super
 +
  WHERE s.Super IS NOT NULL
)
)
-
SELECT acc.ID `Account`, acc.Name,  
+
SELECT Account, Super, Name,  
-
       SUM(CASE WHEN CTE.type = 'Debits'  THEN Amount END) AS Debits,
+
       SUM(Debits) AS Debits,
-
       SUM(CASE WHEN CTE.type = 'Credits' THEN Amount END) AS Credits,
+
       SUM(Credits) AS Credits,
-
       SUM(Amount) AS Net
+
       SUM(Net) AS Net
-
FROM CTE
+
FROM summary
-
JOIN sa_accounts acc ON CTE.account = acc.ID
+
GROUP BY Account, Super, Name
-
  WHERE acc.Report = "PL"
+
ORDER BY Account
-
GROUP BY acc.ID, acc.Name
+
|database=EcoReality}}
|database=EcoReality}}

Revision as of 06:24, 12 May 2020

Contents

This report is in development, and data shown on this page may be missing, poorly organized, inaccurate, or flat-out wrong.


Error while executing input SQL:
ITH RECURSIVE xfers AS (
  SELECT Source AS account,
         0 AS TYPE,
         -Amount AS Amount
  FROM sa_general_journal
  UNION ALL 
  SELECT Destination,
         1,
         Amount
  FROM sa_general_journal gj
),
dbcr AS ( 
  SELECT
      acc.ID `Account`,
      acc.Super,
      acc.Name,
      COALESCE(SUM(CASE WHEN x.type = 0  THEN Amount END), 0) AS Debits,
      COALESCE(SUM(CASE WHEN x.type = 1 THEN Amount END), 0) AS Credits,
      COALESCE(SUM(Amount), 0) AS Net
  FROM sa_accounts acc
  LEFT JOIN xfers x ON x.account = acc.ID
--  WHERE acc.Report = "BS"
--  WHERE acc.Report = "PL"
  GROUP BY acc.ID
),
summary AS (
  SELECT * 
  FROM dbcr
  WHERE Net != 0
  UNION ALL
  SELECT d.Account, d.Super, d.Name, s.Debits, s.Credits, s.Net
  FROM dbcr d
  JOIN summary s ON d.Account = s.Super
  WHERE s.Super IS NOT NULL
)
SELECT Account, Super, Name, 
       SUM(Debits) AS Debits,
       SUM(Credits) AS Credits,
       SUM(Net) AS Net
FROM summary
GROUP BY Account, Super, Name
ORDER BY Account

See Also

Share your opinion


blog comments powered by Disqus
Environmental jobs, green volunteering, good work! Powered by the wind! This server and other
EcoReality operations
are 100% wind powered.
Powered by Mac OS X Powered by Mac MediaWiki Powered by MariaDB Powered by Valentina Studio Pro