Historical profit and loss

From MediaWiki

Revision as of 06:24, 12 May 2020 by Jan Steinman (Talk | contribs)
Jump to: navigation, search

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