SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE dim_dep_agreement ( sk INT, agrmnt_id INT, actual_from_dt DATE, actual_to_dt DATE, client_id INT, product_id INT, interest_rate INT ); INSERT INTO dim_dep_agreement values ( 1, 101, '2015-01-01', '2015-02-20', 20, 305, 3 ), ( 2, 101, '2015-02-21', '2015-05-17', 20, 345, 4 ), ( 3, 101, '2015-05-18', '2015-07-05', 20, 345, 4 ), ( 4, 101, '2015-07-06', '2015-08-22', 20, 539, 6 ), ( 5, 101, '2015-08-23', '9999-12-31', 20, 345, 4 ), ( 6, 102, '2016-01-01', '2016-06-30', 25, 333, 3 ), ( 7, 102, '2016-07-01', '2016-07-25', 25, 333, 3 ), ( 8, 102, '2016-07-26', '2016-09-15', 25, 333, 3 ), ( 9, 102, '2016-09-16', '9999-12-31', 25, 560, 5 ); select * from dim_dep_agreement; with d as ( select *, coalesce( (age(actual_from_dt, lag(actual_to_dt) over (partition by agrmnt_id, client_id, product_id, interest_rate order by sk))), '1 day' ) a from dim_dep_agreement ) select agrmnt_id, min(actual_from_dt) actual_from_dt, max(actual_to_dt), client_id, product_id, interest_rate from d group by agrmnt_id, client_id, product_id, interest_rate, a order by actual_from_dt