Database
Highlighted
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

 ID TEST TRANS DAYS 100 NULL A to B NULL 100 15 No Transition 15 100 16 No Transition 16 100 32 No Transition 32 100 NULL B to A NULL 100 NULL No Transition NULL 100 63 A to B 63

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

 ID MAX_DAYS TRANS DAYS Date 100 NULL A to B NULL 1/1/2011 100 15 No Transition 15 1/2/2011 100 16 No Transition 16 1/3/2011 100 32 No Transition 32 1/4/2011 100 NULL B to A NULL 1/5/2011 100 NULL No Transition NULL 1/6/2011 100 63 A to B 63 1/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

 ID TYPE_OF_TRANS DT MAX 'No Transition' DT 1 NH to HCBS 07/02/2015 NULL 2 No Transition 07/17/2015 NULL 3 No Transition 07/18/2015 NULL 4 No Transition 08/03/2015 NULL 5 HCBS to NH 08/06/2015 NULL 6 No Transition 09/01/2015 NULL 7 NH to HCBS 09/03/2015 NULL 8 HCBS to NH 10/01/2015 NULL 9 No Transition 10/20/2015 02/01/2016 10 No Transition 11/01/2015 02/01/2016 11 No Transition 12/01/2015 02/01/2016 12 No Transition 01/01/2016 02/01/2016 13 No Transition 02/01/2016 02/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 :-)