On Mon Sep 11, 2017 at 08:40:41AM +0200, Frank Millman wrote:
> I could structure it like this (pseudo code) -
>
> SELECT acc_no, acc_name,
> (SELECT SUM(invoice_bal) FROM invoices
> WHERE invoice_date <= date_5) AS 120_days,
> (SELECT SUM(invoice_bal) FROM invoices
> WHERE invoice_date > date_5 AND invoice_date <= date_4) AS 90_days,
> (SELECT SUM(invoice_bal) FROM invoices
> WHERE invoice_date > date_4 AND invoice_date <= date_3) AS 60_days,
> (SELECT SUM(invoice_bal) FROM invoices
> WHERE invoice_date > date_3 AND invoice_date <= date_2) AS 30_days,
> (SELECT SUM(invoice_bal) FROM invoices
> WHERE invoice_date > date_2 AND invoice_date <= date_1) AS current
> FROM debtors_table
>
> This works, but it requires 5 separate scans of the invoice table,
> which is inefficient.
You can achieve the same result with a single table scan using CASE
statements:
SELECT
acc_no,
acc_name,
SUM(
CASE WHEN
invoice_date <= date_5
THEN
invoice_bal
ELSE
0
END
) AS 120_days,
SUM(
CASE WHEN
invoice_date > date_5 AND invoice_date <= date_4
THEN
invoice_bal
ELSE
0
END
) AS 90_days,
-- .......
SUM(
CASE WHEN
invoice_date > date_2 AND invoice_date <= date_1
THEN
invoice_bal
ELSE
0
END
) AS current
FROM
debtors_table
LEFT JOIN
invoices
ON
invoices.debtor_id = debtors_table.id
If your time periods are not exhaustive (covering all time) then you
can perhaps be more efficient again by moving the filtering into the
join clause:
SELECT
acc_no,
acc_name,
SUM(i_90.invoice_bal) AS 90_days,
SUM(i_current.invoice_bal) AS current
FROM
debtors_table
LEFT JOIN
invoices i_90
ON
i_90.debtor_id = debtors_table.id AND
i_90.invoice_date > date_5 AND
i_90.invoice_date <= date_4
LEFT JOIN
invoices i_current
ON
i_current.debtor_id = debtors_table.id AND
i_current.invoice_date > date_2 AND
i_current.invoice_date <= date_1
ORDER BY
...
For the above an index on invoices(debtor_id,invoice_date) would be
appropriate.
Also in case you weren't aware, the SUM() function in my second example
could produce NULL values. You may be better served in that instance by
CAST(TOTAL(i_current.invoice_bal) AS INTEGER).
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
