Window Function w/ a case statement

enter image description hereHelp for a newer SQL analyst: I need to get MAX value from the YearMo column by company, where Prem is zero/null. I created a flag to find YearMo where the sum is 0, indicated by a ‘1’ in the exclude column.

How can I edit my current window function to include a case statement where it would not include YearMo’s that have the flag.

The function: MAX(“YearMo”) OVER (PARTITION BY “Company”) AS “MaxYearMo”

Example Snip of data

Answer

Using CASE expression to build conditional aggregation. “where Prem is zero/null” :

MAX(CASE WHEN Prem IS NULL OR Prem = 0 THEN "YearMo" END) 
    OVER (PARTITION BY "Company") AS "MaxYearMo"

otherwise:

MAX(CASE WHEN Prem <> 0 THEN "YearMo" END) 
    OVER (PARTITION BY "Company") AS "MaxYearMo"