Write a query in which rate was not change in between these dates-CodePudding

Here is input and output.

Write a query in which rate was not change in between these dates.

       input               |                output
===========================|===========================================
date           rate        | startdate         end date         rate
2014-09-18     270         | 2014-09-18       2014-09-19         270
2014-09-19     270         | 2014-09-20       2014-09-22         310
2014-09-20     310         | 2014-09-23       2014-09-23         320
2014-09-21     310         | 2014-09-24       2014-09-24         310
2014-09-22     310         | 2014-09-25       2014-09-25         320
2014-09-23     320         | 2014-09-26       2014-09-26         270
2014-09-24     310         |
2014-09-25     320         |
2014-09-26     270         |

CodePudding user response：

This is a gaps and islands problem. One solution uses the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY date) rn1,
              ROW_NUMBER() OVER (PARTITION BY rate ORDER BY date) rn2
    FROM yourTable
)

SELECT MIN(date) AS startdate, MAX(date) AS enddate, rate
FROM cte
GROUP BY rate, rn1-rn2
ORDER BY startdate;

Demo

CodePudding user response：

with data as
(
  select 1 as Id, '2021/10/01' as date
  union all
  select 1 as Id, '2021/10/02' as date
  union all
  select 1 as Id, '2021/10/03' as date
    union all
  select 2 as Id, '2021/10/04' as date
    union all
  select 2 as Id, '2021/10/05' as date
    union all
  select 2 as Id, '2021/10/06' as date
)
  SELECT
    DISTINCT
    Id,
    FIRST_VALUE(date) OVER (PARTITION BY Id ORDER BY date ) as FirstValue,
    LAST_VALUE (date) OVER (PARTITION BY Id ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LastValue
    from data
    GROUP BY id, date