Exclude NULLs from Min/Max partition by

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Fan

Exclude NULLs from Min/Max partition by

Hi,

 

Does anyone know how to ignore NULLS rows from a partition  by clause?

I'm looking to calculate a MIN partition over id and date but ignoring the NULL rows, something like this but it is not working:

 

SEL

CALENDAR DATE

, ID

, COUNTS_PER_DAY

,  CASE WHEN COUNTS_PER_DAY IS NOT NULL THEN 

                                    MIN(COUNTS_PER_DAY)  OVER ( PARTITION BY  ID  ORDER CALENDAR_DATE             

                                    ROWS  BETWEEN 90 PRECEDING AND 1 PRECEDING )  AS MIN_PROMPTS_LAST_90_DAYS

FROM TABLE A

==============================

Desired Output :

 

CALENDAR DATE------------------id---------------counts per day------------------MIN

12/11/2017-------------------------- A ----------------------- 2   -----------------------     2

12/11/2017--------------------------B -----------------------5  -------------------------    1

13/11/2017--------------------------A---------------------  NULL----------------------- NULL
13/11/2017--------------------------B----------------------- 9    -------------------------   1
14/11/2017--------------------------A -----------------------5   -------------------------   2

14/11/2017--------------------------B-----------------------1   -------------------------   1

 

Thanks,

A

 

 

1 REPLY 1
Ambassador

Re: Exclude NULLs from Min/Max partition by

Aggregate functions ignore NULLs, simply try removing the CASE.