Write a SQL Query to find Start_date and End_date when there is a constant Balance amount for continuous Dates

 

 

 

 

CREATE TABLE BALANCES(

BALANCE NUMBER,

DATES DATE

);INSERT INTO BALANCES VALUES('26000','2020-01-01');

INSERT INTO BALANCES VALUES('26000','2020-01-02');

INSERT INTO BALANCES VALUES('26000','2020-01-03');

INSERT INTO BALANCES VALUES('30000','2020-01-04');

INSERT INTO BALANCES VALUES('30000','2020-01-05');

INSERT INTO BALANCES VALUES('26000','2020-01-06');

INSERT INTO BALANCES VALUES('26000','2020-01-07');

INSERT INTO BALANCES VALUES('32000','2020-01-08');

INSERT INTO BALANCES VALUES('31000','2020-01-09');

 

SELECT

   BALANCE,

   DATES,

   ROW_NUMBER() OVER(ORDER BY DATES ) AS DATE_RANK,

   ROW_NUMBER() OVER(PARTITION BY BALANCE ORDER BY DATES) RANKING,

   ROW_NUMBER() OVER(ORDER BY DATES ) - ROW_NUMBER()OVER(PARTITION BY BALANCE ORDER BY DATES) DIFF

FROM BALANCES ORDER BY DATES;

 

WITH TEMP AS

(

SELECT

BALANCE,

DATES,

ROW_NUMBER() OVER(ORDER BY DATES ) AS DATE_RANK,

ROW_NUMBER() OVER(PARTITION BY BALANCE ORDER BY DATES) RANKING,

ROW_NUMBER() OVER(ORDER BY DATES ) - ROW_NUMBER()OVER(PARTITION BY BALANCE ORDER BY DATES) DIFF

FROM BALANCES ORDER BY DATES

)

SELECT

BALANCE,

MIN(DATES) AS START_DATE,

MAX(DATES) AS END_DATE

FROM TEMP

GROUP BY BALANCE,DIFF;