Update Submission Statistics Endpoints to return NULLs instead of zeros when an agency has no submission for a period



This ticket is follow up work to , which added a row to the ReportingAgencyOverview table for each agency and submission period pair, even if the agency did not make a submission for that period.

Rows for periods where an agency did not make a submission contained zeros. These zeros should be replaced with NULL values to make a distinction between non submitted submissions and submissions with zero values.

Tech Approach

The ReportingAgencyOverview is being populated by the populate_reporting_agency_overview.pymanagement command. This script uses a SQL sequence to generate all period/agency combinations with most values defaulting to zero, and then joins agencies, submissions, and other tables to calculate the values to replace these zeroes with.

With this in mind, the following tech approach may solve this ticket

  • Replace the default zeros in the SQL sequence with NULLs

  • Analyze populate_reporting_agency_overview.py and determine if changing these zeros to NULLs break any functionality. For instance, in Postgres a number plus NULL results in NULL

  • Analyze endpoints making use of the ReportingAgencyOverview to make sure NULLs do not break functionality


  • v2/reporting/agencies/overview - Supports the Statistics by Submission Period page

  • v2/reporting/agencies/{toptier_code}/overview - Supports the Agency Page

  • v2/reporting/agencies/publish_dates- Supports the Updates by Fiscal Year page

Acceptance Criteria

  1.  The Updates by Fiscal Year and Agency endpoints use the following logic to filter out periods:

    1. For FY21 and forward – show all monthly periods

    2. For 2020: Hide P01-02, P04, P05 for ALL agencies

    3. For 2019 and earlier: Should hide P01/P02, P04, P05, P07, P08, P11 in FY17, FY18, and FY19. Monthly submissions were not available during those times. Should also hide P03 for FY17 since there were no quarterly submissions that quarter.

  2. The Updates by Fiscal Year endpoint returns null instead of zero for current_total_budget_authority_amount when no submissions exist for that year. (Empty strings are already being returned for submission dates are already empty strings for missing submissions)

  3. The Statistics by Submission Period page endpoint returns nulls periods an agency did not make a submission for all results fields except the following:

    1. agency_name

    2. abbreviation

    3. toptier_code

    4. agency_id

  4. The Agency page endpoint returns nulls periods an agency did not make a submission for all results fields except the following:

    1. fiscal_year

    2. fiscal_period

  5. API Contracts have been updated to reflect these new nullable fields


Melissa Plooksawasdi
7 days ago

Tested in Staging. Looks good. Nulls are sorted at the bottom of results. Nulls are displayed where there are no submissions.

Seth Stoudenmier
May 3, 2021, 7:06 PM

PR to handle sorting of NULL values:

Seth Stoudenmier
April 30, 2021, 8:11 PM

Follow up PR:

Collins, William [USA]
April 29, 2021, 3:25 PM

Follow up PR:

Collins, William [USA]
April 27, 2021, 9:41 PM






Collins, William [USA]





Story Points