how to ignore the values before and after between the rows in a table

I have a table as like below

I have to add a new column to the table as “value_”, which should contains age values for rows between last TRUE i.e row number 4 (highlighted in green color) value and last record(which has 01-01-9999) and remaining all should be “zero”

like as below

If all values (except last record which have 01-01-9999) are FALSE then we need all the age values like as below

how to achieve this in sql? Could you please help me on this

Answer

If I understand correctly , here is one way:

SELECT * , CASE WHEN maxtrueflag IS NULL THEN age_
            CASE WHEN maxtrueflag IS NOT NULL AND from_>= maxtruefalg THEN age_ 
            ELSE 0 END AS value_
FROM (
    SELECT *, MAX(CASE WHEN flag = TRUE THEN from_ END) OVER() maxtrueflag
    FROM  tableName 
    ) t

​x
 
SELECT * , CASE WHEN maxtrueflag IS NULL THEN age_            CASE WHEN maxtrueflag IS NOT NULL AND from_>= maxtruefalg THEN age_             ELSE 0 END AS value_FROM (    SELECT *, MAX(CASE WHEN flag = TRUE THEN from_ END) OVER() maxtrueflag    FROM  tableName     ) t​

Advertisement

Answer