I tried to do this:
SELECT p.company, p.start, p.yearend, p.idnum,
s.pdno, s.pdend,
CASE WHEN nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) OVER w IS NULL
THEN p.start
ELSE nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) + '1 day'::INTERVAL
END AS spdstart
FROM period AS p,
subperiod AS s
WHERE p.company = s.company AND p.yearend = s.yearend
WINDOW w AS (PARTITION BY p.start ORDER BY s.pdno)
ORDER BY p.yearend, s.pdno;
in order to get these results:
company | start | yearend | idnum | pdno | pdend | spdstart
---------+------------+------------+-------+------+------------+-----------
GBS | 2014-02-01 | 2015-01-31 | 1 | 1 | 2014-04-30 | 2014-02-01
GBS | 2014-02-01 | 2015-01-31 | 1 | 2 | 2014-07-31 | 2014-05-01
GBS | 2014-02-01 | 2015-01-31 | 1 | 3 | 2014-10-31 | 2014-08-01
GBS | 2014-02-01 | 2015-01-31 | 1 | 4 | 2015-01-31 | 2014-11-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 1 | 2015-04-30 | 2015-02-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 2 | 2015-07-31 | 2015-05-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 3 | 2015-10-31 | 2015-08-01
GBS | 2015-02-01 | 2016-01-31 | 2 | 4 | 2016-01-31 | 2015-11-01
(8 rows)
but apparently nesting window functions is not permitted (in 9.3.10).
Is this possible in later versions of PG? or is there any other way to
look at a value from the previous row?
