SQL query to get consecutive months and also find gaps per Client

Question

I am having a roadblock on dataset I am trying to create. I am looking to find the consecutive months of Invoicing for clients.
If there is break in months the count should restart. My end dataset would provide me the max consecutive months, max or total gaps in months.

Here is sample table:

select 'Demo Client' as ClientName,'1' as InvoiceMonthNumber,'20201' InvoiceYearMonth,'2020' InvoiceYear
into #tempa
union select 'Demo Client','2','20202','2020'
union select 'Demo Client','3','20203','2020'
union select 'Demo Client','4','20204','2020'
union select 'Demo Client','5','20205','2020'
union select 'Demo Client','6','20206','2020'
union select 'Demo Client','7','20207','2020'
union select 'Demo Client','10','202010','2020'
union select 'Demo Client','5','20215','2021'
union select 'Demo Client','6','20216','2021'
union select 'Demo Client','7','20217','2021'
union select 'Demo Client','8','20218','2021'
union select 'Demo Client','9','20219','2021'
union select 'Demo Client','10','202110','2021'

select *
from #tempa
where invoiceyear = 2020

select *
from #tempa
where invoiceyear = 2021

Here, For this client, The max no. of consecutive months of invoice would be 7 for year 2020.
The gaps in months is:
month 8,9 in 2020
month 11,12 of 2020
month 1,2,3,4,5 of 2021
so max gap would be of 5 months

I am looking for ideas and logic.

Answer 1

The above code gets the largest gap of all time (including 2020 and 2021).The following code gives the maximum gap for 2020 and 2021 alone, please refer to:

;WITH cte  
 as(select *,(12-COUNT(InvoiceMonthNumber) OVER(PARTITION BY InvoiceYear)) gaps,  
 LEAD(InvoiceMonthNumber) OVER(PARTITION BY InvoiceYear ORDER BY InvoiceMonthNumber) ll,  
 (LEAD(InvoiceMonthNumber) OVER(PARTITION BY InvoiceYear ORDER BY InvoiceMonthNumber)-  
 InvoiceMonthNumber) diff  
 from #tempa)  
 ,cte2 as(select *,(count(diff) over(partition by InvoiceYear)+1) consecutivemonths  
 from cte  
 where diff=1)  
  
 select '2020' [year],ClientName,max(consecutivemonths) maxconsecutivemonths,max(gaps) maxgap  
 from cte2  
 where InvoiceYear=2020  
 group by ClientName  
 union all  
 select '2021',ClientName,max(consecutivemonths) maxconsecutivemonths,max(gaps) maxgap  
 from cte2  
 where InvoiceYear=2021  
 group by ClientName  

Ouput:

Answer 2

Hi @Success Maharjan ，

Welcome to the microsoft tsql Q&A forum!
Please check:

;WITH cte  
as(select *,(12-COUNT(InvoiceMonthNumber) OVER(PARTITION BY InvoiceYear)) gaps,  
LEAD(InvoiceMonthNumber) OVER(PARTITION BY InvoiceYear ORDER BY InvoiceMonthNumber) ll,  
(LEAD(InvoiceMonthNumber) OVER(PARTITION BY InvoiceYear ORDER BY InvoiceMonthNumber)-  
InvoiceMonthNumber) diff  
from #tempa)  
,cte2 as(select *,(count(diff) over(partition by InvoiceYear)+1) consecutivemonths  
from cte  
where diff=1)  
  
select ClientName,max(consecutivemonths) maxconsecutivemonths,max(gaps) maxgap  
from cte2  
group by ClientName  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.