PARTITION using RESET

Database
Enthusiast

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

Enthusiast

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.

Enthusiast

Re: PARTITION using RESET

Thank you so much Dieter!  

Enthusiast

Re: PARTITION using RESET

Hi - Further to my question above, does anyone know how I would get the MAX (DT) for the 'No Transition' records in the TYPE_OF_TRANS column after the last HCBS to NH value as shown in the grid below?  So I would want it to look like the COL MAX 'No Transition' DT below.  The 'No Transition' records in red prior to the last HCBS to NH value should be excluded.

 

Here's what I have now that isn't working...

SELECT

  ID

, TYPE_OF_TRANS

, DT

MAX(CASE WHEN TYPE_OF_TRANS = 'No Transition' THEN 1 ELSE 0 END) OVER (PARTITION BY TYPE_OF_TRANS ORDER BY TRANS_START_DT ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) "MAX 'No Transition' DT"

FROM TABLE1

 

IDTYPE_OF_TRANSDTMAX 'No Transition' DT
1NH to HCBS07/02/2015NULL
2No Transition07/17/2015NULL
3No Transition07/18/2015NULL
4No Transition08/03/2015NULL
5HCBS to NH08/06/2015NULL
6No Transition09/01/2015NULL
7NH to HCBS09/03/2015NULL
8HCBS to NH10/01/2015NULL
9No Transition10/20/201502/01/2016
10No Transition11/01/201502/01/2016
11No Transition12/01/201502/01/2016
12No Transition01/01/201602/01/2016
13No Transition02/01/201602/01/2016
Junior Contributor

Re: PARTITION using RESET

This sould work:

 

CASE 
   WHEN Sum(CASE WHEN TYPE_OF_TRANS = 'HCBS to NH' THEN 1 ELSE 0 END)
        Over (ORDER BY DT DESC
              ROWS Unbounded Preceding) = 0  -- find the last 'HCBS to NH' = 1st in desceding order
   THEN Max(CASE WHEN TYPE_OF_TRANS = 'No Transition' THEN DT END)
        Over ()
END

 

This will assign that date to all rows regardless of TYPE_OF_TRANS, if you want it for 'No Transition' rows only, add AND TYPE_OF_TRANS = 'No Transition' to the WHEN.  

 

Enthusiast

Re: PARTITION using RESET

Awesome that worked!  How did you get so versed in TD Dieter?  Wow!

Junior Contributor

Re: PARTITION using RESET

30+ years of SQL & 20+ years of Teradata :-)