Oracle SQL tutorial
Calculate repo profit and loss for a reporting period using the GREATEST and LEAST functions to determine the correct calculation window.
About this tutorial
This tutorial builds a single Oracle SQL query that calculates Repo P&L for the portion of each transaction that actually overlaps with the reporting period.
The key step is defining the calculation boundaries with GREATEST and LEAST. This lets the same query handle transactions that started before the period, mature during the period, or remain open on the report date.
Use a small parameter CTE for the report start date and report date.
Use GREATEST for the start and LEAST for the end of the P&L calculation.
Apply the daily result only to the number of days that belong to the reporting period.
Repo P&L workflow
This tutorial is the first step in the complete Repo P&L reconciliation workflow.