Maximum number of consecutive trading holidays from a date/calendar table

Question

I am trying to find the maximum number of consecutive trading holidays from a Trading date/calendar table. I have a flag isTradingHoliday = 1 in the TradingDate table that denotes the dates which are trading holidays, otherwise isTradingHoliday = 0. How to know which date range was the most consecutive trading holidays in that TradingDate table?

Answer 1

This sounds like a gaps-and-islands problem. You can find the first date and the count of days using the difference of row numbers. The rest is aggregation and filtering:

select top (1) with ties min(tradingdate) as startdate,
       max(tradingdate) as enddate
from (select c.*,
             row_number() over (order by tradingdate) as seqnum,
             row_number() over (partition by isTradingHoliday order by tradingdate) as seqnum_h
      from calendar c
     ) c
where isTradingHoliday = 1
group by isTradingHoliday, (seqnum - seqnum_h)
order by count(*) desc