Historical profit and loss

From MediaWiki

(Difference between revisions)
Jump to: navigation, search
(Replaced content with "{{profit and loss for|{{CURRENTYEAR}}}} {{historical see also}} Category:Finance Category:Database")
 
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.}}
+
{{profit and loss for|{{CURRENTYEAR}}}}
-
{{#tag:mysql|
+
-
WITH 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
+
-
|database=EcoReality}}
+
{{historical see also}}
{{historical see also}}

Latest revision as of 18:16, 15 May 2020

This information is only available to logged-in users. Please log in or ask for an account to see this information.


See Also

Share your opinion


blog comments powered by Disqus
Personal tools
Get our newsletter!
Name:
Email Address:

disturbance
entry points
help (off site)
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