Historical balance sheet

From MediaWiki

(Difference between revisions)
Jump to: navigation, search
(Hierarchical roll-up)
(Replaced content with "{{balance sheet for|{{CURRENTYEAR}}}} {{historical see also}} Category:Finance Category:Database")
 
(One intermediate revision not shown)
Line 1: Line 1:
-
{{more statistics for|{{CURRENTYEAR}}|balance sheet}}{{note|This report is in development, and data shown on this page may be missing, poorly organized, inaccurate, or flat-out wrong.}}
+
{{balance sheet 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}}
[[Category:Finance]]
[[Category:Finance]]
[[Category:Database]]
[[Category:Database]]

Latest revision as of 18:15, 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