I am building a report for a client using SQL views.
I need to create 4 column, which are dependent:
- Balance Held
- Total Number of units
- Average Rate
- Realized P&L
Average Rate = Balance Held/Total Number of units
Realized P&L = Number of Units * ( Previous Average Rate - Dealt Rate)
Balance Held = Previous Balance Held + Realized PnL + Dealt Rate * Units
UNITS | DEALT RATE | AVERAGE RATE | BALANCE HELD | TOTAL UNITS | REALIZED P&L
1 | 4 | 4 | 4 | 1 | 0
3 | 2 | 4 | 16 | 4 | 6
2 | 3 | 4 | 24 | 6 | 2
2 | 3 | 4 | 32 | 8 | 2
2 | 3 | 4 | 40 | 10 | 2
In Excel it is all fine, however When I am trying to incorporate the above logic into SQL views (I calculate Realized PnL in View1, Balance Held in View 2 and Average Rate in View 3 ), I get the error message about self - referencing of the views. :
View or function vw_PnLReport_by_deal4 contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
Any hint on how to apply this logic in SQL ? which function to use ?
Thank you !