Some reports sources still display wrong last billing type and note
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Evergreen |
New
|
Undecided
|
Unassigned |
Bug Description
As originally reported in bug 1206936, certain reports sources do not consistently show the correct last billing type or note. The views I identified in comment 1 of that ticket are here:
-- money.billable_
-- money.billable_
-- money.open_
-- money.open_
-- money.open_
-- money.transacti
-- money.transacti
-- money.transacti
-- money.transacti
We've hit an issue with a commonly-used PINES report that needs this fixed.
Evergreen 2.11.1-ish
OpenSRF 2.4.1
PostgreSQL 9.4
Ubuntu 14.04 LTS
tags: | added: reports |
Chris,
It looks like we may not have to go full-on window function to fix those. Consider the following test of a redefinition of the money.open_ transaction_ billing_ type_summary view:
-- Given a billable transaction exhibiting the issue, replace XXXX below.
SELECT billing. billing_ type ORDER BY billing.billing_ts) AS last_billing_type, billing. note ORDER BY billing.billing_ts) AS last_billing_note, billing. billing_ ts) AS last_billing_ts, COALESCE( billing. amount, 0::numeric)) AS total_owed billing_ type;
billing.xact,
last(
last(
max(
sum(
FROM money.billing
WHERE billing.voided IS FALSE AND billing.xact = XXXX
GROUP BY billing.xact, billing.
In my testing, this is just as fast as the one not using the ORDER BY clause in the aggregate functions.