Bear with me - that is the first time try that in SQL Server, normally I have been doing that on the front end ;)
I a implementing some analysis on time coded data series. This is not super complicated stuff, but some of it requires some numbers we do not store in the database and that has to be calculated by aggregating the numbers in a specific algorithm IN ORDER.
To give an example:
- I have a list of trades and I need to know the maximum loss we had in the account, so i need to aggregate the plus/minus and then take the most extreme negative and positive.
This can not be pre-calculated due to dynamic filtering - there are a number of filters that can be applied to the data.
So far - past - I pulled the data to the application, now for the standard stuff I plan to try to keep that in the sql server.
My problem now is - I can see how that works (acceptable) in SQL Server:
[TradeBegin], Pnl, SUM (Pnl) OVER (ORDER BY [TradeBegin] ROWS UNBOUNDED PRECEDING) [Total]
But if I put that into a view... and then filter out rows, the Sum is still calcualted from the beginning. And I need a view because I want (need) to map that standard analysis data into an ORM (so dynamic SQL is out). Anyone an idea how to do that?