Hi All - I'm trying to return the first MAX value of the DAYS col below for TRANS values 'A to B' and 'No Transition' before a change in the TRANS column of 'B to A'. As you can see from the example below, there is a later TRANS value of 'A to B' (shown in red), but I don't want to include this value. In other words, I only want to show the MAX of the first set of records before the TRANS value change of 'B to A'. So in the case below, I want to return 32, not 63. Any assistance is greatly appreciated!
This is what I have so far but it's not working....
, CASE WHEN TRANS = 'A to B' AND MAX(DAYS) OVER (PARTITION BY ID ORDER BY TRANS RESET WHEN TRANS = 'B to A' ROWS UNBOUNDED PRECEDING) AS TEST
|100||NULL||A to B||NULL|
|100||NULL||B to A||NULL|
|100||63||A to B||63|
Solved! Go to Solution.
How do you determine the order of rows?
Currently there's no way to do that calculation, but I assume that TRANS is not an column, but the result of a calculation based on another OLAP function. Can you show the actual source code including the order column (probably a Date ot Timestamp)?
Hi Dieter - Yes TRANS is a column I created and the partition can be ordered by a DATE field (sorry for not including initially). I included the code below. Is there no way to set a window to include only the records until the first Trans change?
, CASE WHEN TRANS IN (‘A to B’) AND MAX(DAYS) OVER (PARTITION BY COL A ORDER BY DATE RESET WHEN TRANS = ‘B to A’ ROWS UNBOUNDED PRECEDING) MAX_DAYS
|100||NULL||A to B||NULL||1/1/2011|
|100||NULL||B to A||NULL||1/5/2011|
|100||63||A to B||63||1/7/2011|