Get dates where data increments from consective months

Question

Hi

Here is my data sample:

Declare @tmp table (ID int, MonthName nvarchar(100), score decimal(18,2))
INsert into @tmp
SELECT 9 ,'Apr 2020', 96.33
union
SELECT 10 ,'May 2020', 96.83
union
SELECT 11 ,'Jun 2020', 96.42
union
SELECT 12 ,'Jul 2020', 97.00
union
SELECT 13 ,'Aug 2020', 96.17
union
SELECT 14 ,'Sep 2020', 95.69
union
SELECT 15 ,'Oct 2020', 95.28
union
SELECT 16 ,'Nov 2020', 94.70
union
SELECT 17 ,'Dec 2020', 93.95
union
SELECT 18 ,'Jan 2021', 97.32

Select * from @tmp

What I want to do is end up with a result set from the last 9 months where the score has decreased consecutively for 6 months

So I would expect:

StartDate End Date
Jul 2020 Dec 2020

Any ideas?

Thank you

Answer 1

Hi @Satnam Nandra ,

Please refer below updated one and check whether it is helpful to you.

;with cte as(  
 select  ID,MonthName,score,1 as ChainLength  
 from    @tmp  
 where   ID = (select min(ID) from @tmp)  
 union all  
 select  cur.ID,cur.MonthName,cur.score  
 ,case when  cur.score < prev.score  then prev.ChainLength + 1 else 1 end  
 from    cte prev  
 join    @tmp cur  
 on      prev.ID + 1 = cur.ID  
 )  
 ,cte1 as (  
 select a.MonthName StartDate ,b.MonthName EndDate ,b.ChainLength from cte a   
 inner join (select * from cte where ChainLength>=2) b on a.ID+b.ChainLength-1=b.ID)  
 select * from cte1 a  
 where ChainLength=  
 (select max(ChainLength) from cte1 b where b.StartDate=a.StartDate group by StartDate)  

Output:

StartDate	EndDate	ChainLength  
May 2020	Jun 2020	2  
Jul 2020	Dec 2020	6  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Satnam Nandra ,

Welcome to Microsoft Q&A!

Please also refer below and check whether it is working to you.

;with cte as(  
select  ID,MonthName,score,1 as ChainLength  
from    @tmp  
where   ID = (select min(ID) from @tmp)  
union all  
select  cur.ID,cur.MonthName,cur.score  
,case when  cur.score < prev.score  then prev.ChainLength + 1 else 1 end  
from    cte prev  
join    @tmp cur  
on      prev.ID + 1 = cur.ID  
)  
select a.MonthName StartDate ,b.MonthName EndDate  from cte a   
inner join (select * from cte where ChainLength=6) b on a.ID+5=b.ID  

Output:

StartDate	EndDate  
Jul 2020	Dec 2020  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

; WITH CTE AS (
   SELECT ID, MonthName,  
          sign (score - LAG(score) OVER(ORDER BY ID)) AS sign
   FROM  @tmp
), WindowSix AS (
   SELECT ID, MonthName, LAG(MonthName, 5) OVER(ORDER BY ID) AS MonthSix, 
          SUM(sign) OVER (ORDER BY ID ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS WindowSign
   FROM   CTE
)
SELECT MonthName, MonthSix
FROM   WindowSix
WHERE  WindowSign = -5