teradata, reset when, partition by, order by

I need help understanding the below code. I have never seen reset when used in Teradata. What does RESET WHEN do in Teradata? I understand the partition and order by part. I was also unsure why this wasn’t partitioned by PARTITION BY A.ACCT_DIM_NB, A.DAY_TIME_DIM_NB ORDER BY A.TXN_POSTING_SEQ . Also, is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW just using the whole partitioned window?

Removed

Answer

I was also unsure why this wasn’t partitioned by PARTITION BY Y.ACCT_DIM_NB, Y.DAY_TIME_DIM_NB ORDER BY Y.DAY_TIME_DIM_NB, Y.TXN_POSTING_SEQ

Don’t know, but this would return a different result (and Y.DAY_TIME_DIM_NB is not needed in ORDER BY because it’s already partitioned by it)

Also, is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW just using the whole partitioned window?

It’s exactly the same as ROWS UNBOUNDED PRECEDING, i.e. a syntax variation for a Cumulative Max. The lpartition is ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

What does RESET WHEN do in Teradata?

The RESET WHEN is a Teradata extension for dynamically adding partitions, it’s a shorter syntax for two (in your case) or three nested OLAP functions:

-- using RESET WHEN
MAX(A.RUN_BAL_AM)
OVER (PARTITION BY A.ACCT_DIM_NB
      ORDER BY A.DAY_TIME_DIM_NB, A.TXN_POSTING_SEQ 
      RESET WHEN A.CS_TXN_CD NOT IN ('072','075','079','107','111','112','139','181','318') 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS  EOD_BAL_AM



-- Same result using Standard SQL
SELECT  
   Max(A.RUN_BAL_AM)
   Over (PARTITION BY A.ACCT_DIM_NB, dynamic_partition
         ORDER BY A.DAY_TIME_DIM_NB, A.TXN_POSTING_SEQ 
         ROWS BETWEEN Unbounded Preceding AND CURRENT ROW) AS  EOD_BAL_AM

FROM 
 ( 
   SELECT
      -- this cumulative sum over 0/1 assigns a new value for each series of rows based on the CASE
      Sum(CASE WHEN A.CS_TXN_CD NOT IN ('072','075','079','107','111','112','139','181','318') THEN 1 ELSE 0 end)
      Over (PARTITION BY A.ACCT_DIM_NB, dynamic_partition
            ORDER BY A.DAY_TIME_DIM_NB, A.TXN_POSTING_SEQ 
            ROWS Unbounded Preceding) AS dynamic_partition
   FROM ...
 ) AS dt