Jennifer Mackown <niftyshellsuit@outlook.com> writes:
> Hi,
> I have a problem with getting a table to display in the way I want it to. It's one of those things that looks so
simpleI should be able to do it in 5 minutes, but I've been working on it all afternoon and I'm getting nowhere!!
> What I have is the following:
> Date Firstday Lastday2014/03/12 1 12014/03/18 1
02014/03/19 0 12014/03/21 1 1
>
> And what I need to see is this:
> Firstday Lastday2014/03/12 2013/03/122014/03/18 2013/03/192014/03/21 2013/03/21
>
>
> Can anyone help?
WITH tmp (id, firstday, lastday) AS ( SELECT row_number() OVER (PARTITION BY t1.date ORDER BY t2.date), t1.date,
t2.dateFROM tbl t1 JOIN tbl t2 ON t2.date >= t1.date AND t2.lastday = 1 WHERE t1.firstday = 1
)
SELECT id, firstday, lastday
FROM tmp
WHERE id = 1
ORDER BY firstday, lastday
