Search code examples
sqlwindow-functionscase-statement

Postgresql complicated case statement

I need to create a case statement that will return the following

Cust_id   Indicator    Date       Case

101          Y        5/1/2014    5/1/2014
101          Y        5/4/2014    5/4/2014
101          Y        5/8/2014    5/8/2014
101          N        5/8/2014    5/8/2014
101          N        5/12/2014   5/8/2014
101          N        5/14/2014   5/8/2014
101          Y        5/18/2014   5/18/2014

The Indicator column will alternate between Y and N. When the column is a Y it grabs the date and returns it in the case column. When the previous indicator is a Y and the current indicator is a N, it grabs the previous date and that date will continue on until another Y encountered. On the above example I'm not sure how to make 5/8/2014 continue on until I hit the next Y (5/18/2014).

Sorry this is complicated but the above table should help clarify. I think I need to use a subquery and a window function, but I'm all out of ideas. I've been working on this for hours, and my co-workers do not have ideas, so any help is very much appreciated.

Thank you in advance.

Solution

  • YOu can do this by doing a conditional, cumulative maximum value:

    select cust_id, indicator, date,
       max(case when indicator = 'y' then date end) over (partition by cust_id
                                                          order by date) as NewCol
from table t;