question

SuccessMaharjan-7947 avatar image
0 Votes"
SuccessMaharjan-7947 asked EchoLiu-msft answered

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

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.



sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

1 Vote 1 ·
EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered

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:
146504-image.png



image.png (3.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered SuccessMaharjan-7947 commented

Hi @SuccessMaharjan-7947

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@EchoLiu-msft I am getting 6 as max gap in months but I think the max gap would be 4 because of missing months on year 2021 for months 1,2,3 and 4.

0 Votes 0 ·

sorry for the confusion in my question, Would it be possible to get the max gaps in consecutive gaps as well?

0 Votes 0 ·