PARTITION using RESET

Database

PARTITION using RESET

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....

SELECT

           ID

,         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

  

IDTESTTRANSDAYS
100NULLA to BNULL
10015No Transition15
10016No Transition16
10032No Transition32
100NULLB to ANULL
100NULLNo TransitionNULL
10063A to B63

Accepted Solutions
Junior Contributor

Re: PARTITION using RESET

You can restrict the rows before the 1st ‘B to A’ row using 

QUALIFY
   Max(CASE WHEN trans IN ('B TO A') THEN 1 ELSE 0 END)
   Over (PARTITION BY ID ORDER BY date ROWS Unbounded Preceding) = 0

 Put this in a Derived Table and add a simple MAX OVER in the outer level.

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: PARTITION using RESET

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)?

Re: PARTITION using RESET

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? 

SELECT

ID

,         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

, TRANS

, DAYS

, Date

FROM TBL1

 

IDMAX_DAYSTRANSDAYSDate
100NULLA to BNULL1/1/2011
10015No Transition151/2/2011
10016No Transition161/3/2011
10032No Transition321/4/2011
100NULLB to ANULL1/5/2011
100NULLNo TransitionNULL1/6/2011
10063A to B631/7/2011
Junior Contributor

Re: PARTITION using RESET

You can restrict the rows before the 1st ‘B to A’ row using 

QUALIFY
   Max(CASE WHEN trans IN ('B TO A') THEN 1 ELSE 0 END)
   Over (PARTITION BY ID ORDER BY date ROWS Unbounded Preceding) = 0

 Put this in a Derived Table and add a simple MAX OVER in the outer level.

Re: PARTITION using RESET

Thank you so much Dieter!