schedule_breakdown
Group NYC capital projects by agency, borough, phase, or category to count schedules or average variance, with role-aware attribution and deduplication by entity grain.
Instructions
Counts/averages of schedule metrics by agency/sponsor/borough/phase/category.
agency scopes to one agency; agency_role ('auto'|'sponsor'|'managing') picks owner
vs builder lens (auto: sponsor, except DDC/DCAS/EDC -> managing). Category grouping
counts a PID once in EACH of its categories (non-additive). Report neutral, signed
variance.
Interpretation rules:
[grain] Schedule questions use PID; budget questions use the BUDGET LINE key (managing_agency, fms_id). The same FMS ID under different holders is distinct lines. Schedule history is PID x reporting_period; budget history is budget line x reporting_period; fiscal-year budgets also key on fiscal_year. The schedule source has no fms_id; budget sources have no pid. The combined source repeats rows across PID-budget links and location splits: deduplicate at the requested entity grain before counting or summing. Never compare budgets using fms_id alone.
[period_basis] Reporting-period basis: state the returned period for every count, total or ranking and both periods for comparisons. Period aggregates default to the latest complete snapshot, which may precede a partially published newer period. fms_location, fms_sponsor and lifetime_budget_variance are all-history dimensions without reporting_period: use them for enrichment or lifetime figures, never as a single period's inventory.
[agency_roles] Agency attribution is role-aware: an agency's projects mean its sponsor (owner) view, except DDC/DCAS/EDC default to managing (builder). State the returned agency_scope. managing_agency is the executor on schedules and the budget-holder on budgets; a budget-only holder is not a schedule executor. list_agencies exposes is_schedule_executor. For sponsor-scoped budget totals, use a semi-join to fms_sponsor; a value-bearing join can multiply lines. Co-owner totals can overlap and must not be added together.
[categories] Classify with the curated category_dim, not project-name searches: specific ten-year labels/FMS prefixes precede sponsor routing, then generic facility keywords and Other. Categories key on (managing_agency, fms_id); institution owner rules can use all-history ownership. Schedule category_scope='current' uses each PID's current funding links; 'all_history' includes former links. A PID can count in multiple categories, so schedule category counts are non-additive; each budget line has one category.
[signed_values] Report neutral, SIGNED changes: "moved 45 days later" or "budget decreased $2M". Do not echo loaded terms in the answer. "slippage" means positive schedule change and "overrun" means positive budget growth; neither includes the decreasing side. Preserve the returned direction and metric basis.
[schedule_coverage] Schedule totals and cumulative variance use dashboard-aligned schedule_history. source_schedule_history retains native observations absent from that population; schedule_source_coverage reconciles them. State the schedule universe and cumulative basis. Parenthesized phases are no-schedule reasons. Respect excluded variance artifacts and missing/invalid-duration counts; forward duration statistics exclude reversed dates.
[presence] Presence in a selected reporting snapshot means reportable at that period. There is no separate active flag. Historical presence or a latest-known row does not prove presence in the current complete snapshot. A completed project can retain an open budget line for years; presence does not mean construction is in progress.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| agency | No | ||
| metric | No | count | |
| period | No | current | |
| group_by | Yes | ||
| statistic | No | count | |
| agency_role | No | auto |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| groups | Yes | ||
| metric | Yes | ||
| period | Yes | ||
| provenance | Yes | ||
| interpretation_rules | Yes |