Renewable Curtailment Settlement and Lost Generation Query

Role: Principal Energy Settlement Database Architect with deep expertise in renewable energy contract auditing and PPA billing reconciliation.

Context

• Asset management entity: {{asset_owner_name}}
• Unconstrained generation log table: {{generation_log_table}}
• Curtailment directive log table: {{curtailment_event_table}}
• Plant asset identifier: {{asset_id_column}}
• Energy measurement column: {{curtailed_mwh_column}}
• Target settlement interval: {{reporting_quarter}}

Task

Construct an audit-grade SQL reconciliation script that joins {{generation_log_table}} with {{curtailment_event_table}} to compute total lost generation in {{curtailed_mwh_column}} across {{reporting_quarter}} for review by {{asset_owner_name}}.

Method

1. Define time boundary predicates for {{reporting_quarter}} to bound all source record scans efficiently.
2. Standardize interval timestamps between expected generation and grid curtailment instruction tables.
3. Join {{generation_log_table}} and {{curtailment_event_table}} on {{asset_id_column}} and overlapping event timestamps using an explicit inner join.
4. Calculate the delta between theoretical baseline capacity and actual constrained generation for each discrete event.
5. Categorize curtailment causes into economic dispatches, reliability interventions, and transmission bottlenecks based on event reason codes.
6. Aggregate total {{curtailed_mwh_column}} by asset identifier, curtailment classification, and calendar month.
7. Produce a summary audit trail including event counts, total curtailed MWh, and average curtailment duration in hours.

Constraints

• MUST join tables using strict timestamp range overlap conditions rather than imprecise equality joins.
• MUST NOT double-count curtailment volumes where multiple event codes overlap in a single generation interval.
• Financial and volume sums must use ROUND() or NUMERIC precision casts to avoid floating-point drift.
• All generated SQL must run without schema modifications or temporary table creation privileges.

Output format

Deliver an end-to-end SQL audit script:

1. SQL block starting with comment block listing settlement parameters.
2. CTE pipeline normalizing timestamps, isolating intervals, and resolving overlaps.
3. Final reporting query yielding asset ID, event count, total curtailed MWh, and classified root cause.
4. A brief data validation checklist (3 bullet points) for verifying output totals.

Self-review

• Confirm every variable ({{asset_owner_name}}, {{generation_log_table}}, {{curtailment_event_table}}, {{asset_id_column}}, {{curtailed_mwh_column}}, {{reporting_quarter}}) is present in the prompt script body.
• Verify interval overlap logic prevents multi-record Cartesian explosions.
• Ensure all numeric aggregations properly handle null curtailed values with COALESCE.