Get Data by calculation instead of script
I want to implement a feature in my database to check on past performance, whether under certain conditions stock first me stop (bottom price) or target (top price).
To make things clear, I will use such example:
- Let's say, that today's date is 2014-05-05. And the stock (field calc::close) now trades at $89.85. I want to check if during the next 20 records (next records in sequence) stock first:
1) reached bottom ($88.20 or any number in field calc::stop, check field is the same calc::close in the next 20 records)
2) reached top ($94.62 or any number in field calc::target, check field is the same calc::close in the next 20 records)
3) reached none of them, meaning that stock neither hit $88.20 or $94.62 and traded in between those 2.
In a script it wouldn't be a hard thing to do. But how to do that in calculation. I'd like the field calc::result to store one of these 3 words: 1) 'bottom' (if first reached bottom), 2) 'top' (if first reached top), or 3) 'none' (if none of them was reached)
Note, that you cannot play with If (Min(calc_20records::close)<=88.20; "bottom" or If (Max(calc_20records::close)>=94.62; "top", because you reach both top and bottom during next 20 records, but what matters here - which of them came FIRST (if any of them came, if none - then it's 'none')
So how can this be done?