mirror of
https://github.com/GSA/notifications-api.git
synced 2026-05-17 23:34:05 -04:00
73cdec43c0
previously we were looking at the chargeable units from within the subquery, and then subtracting those from the free allowance to get the free allowance remaining (sms_remainder). this is fine for the org and service usage reports, which query for an entire financial year. however, the platform admin report is used for smaller periods, generally monthly. this has the problem when we're querying, eg, may 1st to 30th. Lets say a service has a free allowance of 10000 and sends 4000 messages in april and 5000 in may. we should be reporting their sms remainder as 1000. However, when joining to the sms billing subquery we want to filter out rows not in may, so that we can report on their usage that month only. So we put in the bst_date filter - this means that the "sms_billable_units", "chargeable_sms", and "sms_cost" only report on that month. That's good. But remainder we were just looking at chargeable sms and subtracting from the free allowance, ignoring however much had been sent in april. To solve this, move the free allowance remainder calc into the subquery (which runs on the entire financial year, so has context about april's usage as well). Essentially the chargeable_units_used_before_this_row, plus this row.