Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
6  
The question is not amazingly clear, but if you need to filter rows before applying a window function, consider using an in-line table-valued function (parameterized view) instead of a view. Another option might be a user-defined aggregate. Perhaps adding a small example to the question would help make the requirement clearer? –  Paul White May 28 '13 at 4:14
add comment

1 Answer

Not sure if I understand correctly what you're asking (it would be better if you've shown source data and the desired output), however, I have a feeling that this is what you may be looking for:

[TradeBegin],
Pnl,
SUM (Pnl) OVER (
  PARTITION BY [LetsSayAccountNumber],[WhateverElse]
  ORDER BY [TradeBegin] 
  ROWS UNBOUNDED PRECEDING
)  [Total]
share|improve this answer
    
I suggest you read again about the last sentences - where i talk about filtering. –  TomTom May 28 '13 at 3:38
6  
OK, thank you for your suggestion. I did that, still doesn't make a lot of sense. Now I suggest you provide sample data and the desired output. How'bout that? –  mustaccio May 28 '13 at 11:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.