I have a result set that looks something like this:
| customer | flag | date_from | date_to |
|---|---|---|---|
| ABC123 | Y | 22/01/2020 | 21/02/2021 |
| ABC123 | N | 22/02/2021 | 31/03/2021 |
| ABC123 | Y | 01/04/2021 | 30/09/2021 |
| ABC123 | Y | 01/10/2021 | 31/03/2022 |
| ABC123 | Y | 01/04/2022 | 30/09/2022 |
| ABC123 | Y | 01/10/2022 | 01/01/9999 |
I want to 'flatten' it so that it outputs this:
| customer | flag | date_from | date_to |
|---|---|---|---|
| ABC123 | Y | 22/01/2020 | 21/02/2021 |
| ABC123 | N | 22/02/2021 | 31/03/2021 |
| ABC123 | Y | 01/04/2021 | 01/01/9999 |
Is this possible?
CodePudding user response：
This is a gaps and islands problem. One approach uses the difference in row numbers method:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY date_from) rn1,
ROW_NUMBER() OVER (PARTITION BY customer, flag ORDER BY date_from) rn2
FROM yourTable
)
SELECT
customer,
flag,
MIN(date_from) AS date_from,
MAX(date_to) AS date_to
FROM cte
GROUP BY
customer,
flag,
rn1 - rn2
ORDER BY
MIN(date_from);