Editing ray

Title / Description

Code
SELECT sd.qbclass, Sum(sd.amount) AS invoiceamount, Sum(scd1.amount) AS paymentsperiod1, Sum(scd2.amount) AS paymentsperiod2, Sum(scd3.amount) AS paymentsperiod3 FROM studentdebit AS sd LEFT JOIN (SELECT studentcreditdetail.studentdebitid, studentcreditdetail.amount, studentcredit.date, credittype.credittype FROM studentcreditdetail INNER JOIN studentcredit ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid AND studentcredit.obsolete = 0 /* Not Deleted */ AND studentcredit.status = 1 /* Successful */ /* PERIOD 1 */ AND studentcredit.date < Now() /* PERIOD 1 */ AND studentcredit.date > Now() - INTERVAL 1 MONTH LEFT JOIN credittype USING(credittypeid)) AS scd1 ON sd.studentdebitid = scd1.studentdebitid LEFT JOIN (SELECT studentcreditdetail.studentdebitid, studentcreditdetail.amount, studentcredit.date, credittype.credittype FROM studentcreditdetail INNER JOIN studentcredit ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid AND studentcredit.obsolete = 0 /* Not Deleted */ AND studentcredit.status = 1 /* Successful */ /* PERIOD 2 */ AND studentcredit.date < Now() - INTERVAL 1 MONTH /* PERIOD 2 */ AND studentcredit.date > Now() - INTERVAL 2 MONTH LEFT JOIN credittype USING(credittypeid)) AS scd2 ON sd.studentdebitid = scd2.studentdebitid LEFT JOIN (SELECT studentcreditdetail.studentdebitid, studentcreditdetail.amount, studentcredit.date, credittype.credittype FROM studentcreditdetail INNER JOIN studentcredit ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid AND studentcredit.obsolete = 0 /* Not Deleted */ AND studentcredit.status = 1 /* Successful */ /* PERIOD 3 */ AND studentcredit.date < Now() - INTERVAL 2 MONTH /* PERIOD 3 */ AND studentcredit.date > Now() - INTERVAL 3 MONTH LEFT JOIN credittype USING(credittypeid)) AS scd3 ON sd.studentdebitid = scd3.studentdebitid WHERE sd.obsolete = 0 /* Not Deleted */ AND sd.status = 0 /* Normal */ /* Exclude Voided Invoices */ AND sd.adjustsdebitid IS NULL AND sd.studentdebitid NOT IN (SELECT adjustsdebitid FROM studentdebit WHERE adjustsdebitid IS NOT NULL) /* FULL PERIOD */ AND sd.DATE < Now() /* FULL PERIOD */ AND sd.DATE > Now() - INTERVAL 3 MONTH GROUP BY sd.qbclass /* Formatting only */ UNION ALL SELECT '---', '---', '---', '---', '---' /* Payment Types Summary */ UNION ALL SELECT credittype, invoiceamount, Sum(paymentsperiod1), Sum(paymentsperiod2), Sum(paymentsperiod3) FROM (SELECT scd.credittype, '' AS invoiceamount, Sum(scd.amount) AS paymentsperiod1, '' AS paymentsperiod2, '' AS paymentsperiod3 FROM studentdebit AS sd INNER JOIN (SELECT studentcreditdetail.studentdebitid, studentcreditdetail.amount, studentcredit.date, credittype.credittype FROM studentcreditdetail INNER JOIN studentcredit ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid AND studentcredit.obsolete = 0 /* Not Deleted */ AND studentcredit.status = 1 /* Successful */ /* PERIOD 1 */ AND studentcredit.date < Now() /* PERIOD 1 */ AND studentcredit.date > Now() - INTERVAL 1 MONTH LEFT JOIN credittype USING(credittypeid)) AS scd ON sd.studentdebitid = scd.studentdebitid WHERE sd.obsolete = 0 /* Not Deleted */ AND sd.status = 0 /* Normal */ /* Exclude Voided Invoices */ AND sd.adjustsdebitid IS NULL AND sd.studentdebitid NOT IN (SELECT adjustsdebitid FROM studentdebit WHERE adjustsdebitid IS NOT NULL) AND sd.DATE < Now() AND sd.DATE > Now() - INTERVAL 3 MONTH GROUP BY scd.credittype UNION ALL SELECT scd.credittype, '' AS invoiceamount, '' AS paymentsperiod1, Sum(scd.amount) AS paymentsperiod2, '' AS paymentsperiod3 FROM studentdebit AS sd INNER JOIN (SELECT studentcreditdetail.studentdebitid, studentcreditdetail.amount, studentcredit.date, credittype.credittype FROM studentcreditdetail INNER JOIN studentcredit ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid AND studentcredit.obsolete = 0 /* Not Deleted */ AND studentcredit.status = 1 /* Successful */ /* PERIOD 2 */ AND studentcredit.date < Now() - INTERVAL 1 MONTH /* PERIOD 2 */ AND studentcredit.date > Now() - INTERVAL 2 MONTH LEFT JOIN credittype USING(credittypeid)) AS scd ON sd.studentdebitid = scd.studentdebitid WHERE sd.obsolete = 0 /* Not Deleted */ AND sd.status = 0 /* Normal */ /* Exclude Voided Invoices */ AND sd.adjustsdebitid IS NULL AND sd.studentdebitid NOT IN (SELECT adjustsdebitid FROM studentdebit WHERE adjustsdebitid IS NOT NULL) AND sd.DATE < Now() AND sd.DATE > Now() - INTERVAL 3 MONTH GROUP BY scd.credittype UNION ALL SELECT scd.credittype, '' AS invoiceamount, '' AS paymentsperiod1, '' AS paymentsperiod2, Sum(scd.amount) AS paymentsperiod3 FROM studentdebit AS sd INNER JOIN (SELECT studentcreditdetail.studentdebitid, studentcreditdetail.amount, studentcredit.date, credittype.credittype FROM studentcreditdetail INNER JOIN studentcredit ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid AND studentcredit.obsolete = 0 /* Not Deleted */ AND studentcredit.status = 1 /* Successful */ /* PERIOD 3 */ AND studentcredit.date < Now() - INTERVAL 2 MONTH /* PERIOD 3 */ AND studentcredit.date > Now() - INTERVAL 3 MONTH LEFT JOIN credittype USING(credittypeid)) AS scd ON sd.studentdebitid = scd.studentdebitid WHERE sd.obsolete = 0 /* Not Deleted */ AND sd.status = 0 /* Normal */ /* Exclude Voided Invoices */ AND sd.adjustsdebitid IS NULL AND sd.studentdebitid NOT IN (SELECT adjustsdebitid FROM studentdebit WHERE adjustsdebitid IS NOT NULL) AND sd.date < Now() AND sd.date > Now() - INTERVAL 3 MONTH GROUP BY scd.credittype) AS ct GROUP BY ct.credittype

Author

Highlight as C C++ CSS Clojure Delphi ERb Groovy (beta) HAML HTML JSON Java JavaScript PHP Plain text Python Ruby SQL XML YAML diff code