This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find the start date and end date range for the same market rank and if market rank change, only generates a new range row.
Input data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
rankdate rankno ---------- ----------- 2017-01-01 24000 2017-02-01 24000 2017-03-01 26000 2017-04-01 26000 2017-05-01 26000 2017-06-01 26000 2017-07-01 29000 2017-08-01 29000 2017-09-01 29000 2017-10-01 29500 2017-11-01 29500 2017-11-01 30000 |
Expected output:
|
1 2 3 4 5 6 7 |
StartDate EndDate rankno ---------- ---------- ----------- 2017-01-01 2017-02-01 24000 2017-03-01 2017-06-01 26000 2017-07-01 2017-09-01 29000 2017-10-01 2017-11-01 29500 2017-11-01 2017-11-01 30000 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE MarketRank ( rankdate date ,rankno int ) GO INSERT INTO MarketRank VALUES ('2017-01-01', 24000) ,('2017-02-01', 24000) ,('2017-03-01', 26000) ,('2017-04-01', 26000) ,('2017-05-01', 26000) ,('2017-06-01', 26000) ,('2017-07-01', 29000) ,('2017-08-01', 29000) ,('2017-09-01', 29000) ,('2017-10-01', 29500) ,('2017-11-01', 29500) ,('2017-11-01', 30000) GO |
Solution 1:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
WITH CTE1 AS ( SELECT rankdate , rankno , ROW_NUMBER() OVER (ORDER BY rankdate) Rnk FROM MarketRank ) ,CTE2 AS ( SELECT *, CASE WHEN rankno = lag(rankno) over(order by rnk) THEN 0 ELSE 1 END rankcols FROM CTE1 c2 ) ,CTE3 AS ( SELECT *,SUM(rankcols) over(order by rnk) Grouper FROM CTE2 c2 ) SELECT MIN(rankdate) StartDate ,MAX(rankdate) EndDate ,MAX(rankno) rankno FROM CTE3 GROUP BY Grouper |
Solution 2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
WITH CTE1 AS ( SELECT rankdate , rankno , ROW_NUMBER() OVER (ORDER BY rankdate) Rnk FROM MarketRank ) ,CTE2 AS ( SELECT *, CASE WHEN rankno = ( SELECT rankno from CTE1 c3 WHERE c3.rnk = ( SELECT MAX(c1.rnk) FROM CTE1 c1 WHERE c1.rnk < c2.rnk )) THEN 0 ELSE 1 END Identifier ,SUM(CASE WHEN rankno = ( SELECT rankno from CTE1 c3 WHERE c3.rnk = ( SELECT MAX(c1.rnk) FROM CTE1 c1 WHERE c1.rnk < c2.rnk )) THEN 0 ELSE 1 END) OVER (ORDER BY rnk ) rankcols FROM CTE1 c2 ) SELECT MIN(rankdate) StartDate , MAX(rankdate) EndDate , MAX(rankno) rankno FROM CTE2 GROUP BY rankcols |
