Search code examples
sqlfinance

SQL How to Calculate single day Gain/Loss from month-to-date tables

I have a table that has Total Gain loss on a month to date basis with various portfolios, security identifiers, and Business Units. Every month the Period Start Date gets refreshed to the start of the new month but the table will typically include an entire quarter's worth of data at a time. How can I write a query that calculates the single day Gain Loss for all "End Dates" grouped by Portfolio, Ticker and Business unit?

Portfolio   Fund Legal Entity   SECURITY IDENTIFIER BUSINESS UNIT   GAIN/LOSS   Start Date  End Date
Portfolio1  LegalEntity1    Ticker1 PUBLIC   (4,760,588.0)  1-Feb   27-Feb
Portfolio1  LegalEntity1    Ticker1 PUBLIC   9,123,144.0    1-Feb   28-Feb
Portfolio1  LegalEntity1    Ticker1 PUBLIC   7,470,013.0    1-Mar   1-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   24,466,723.0   1-Mar   2-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   4,272,096.0    1-Mar   3-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   14,043,454.0   1-Mar   4-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   17,631,760.0   1-Mar   5-Mar
Solution

  • You can use lag if your dbms supports:

    SELECT Portfolio, Fund_Legal_Entity, SECURITY_IDENTIFIER,
       BUSINESS_UNIT, GAIN_LOSS, Start_Date, End_Date,
       (gain_loss-lag(GAIN_LOSS) OVER (PARTITION BY Portfolio,  
       Fund_Legal_Entity, SECURITY_IDENTIFIER, BUSINESS_UNIT 
       ORDER BY end_date)) Daily_gain_loss
FROM tablename