question

This is where a tally table or date table comes in handy. If you create a date table with an additional column which marks when there is a bank holiday you can sum the amount of days either with a cross apply or a correlated query. /* create a load of temp tables for example purposes */ CREATE TABLE #DateTally ( [Date] DATE , IsHoliday BIT ); CREATE TABLE #DateHoliday ( [Date] DATE ); CREATE TABLE #DateExample ( [Date] DATE ); ; WITH lv0 AS ( SELECT 0 AS g UNION ALL SELECT 0 ), lv1 AS ( SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b ), lv2 AS ( SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b ), lv3 AS ( SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b ), lv4 AS ( SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b ), lv5 AS ( SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b ), Nums AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS n FROM lv5 ) /* Load Tally with 10000 sequential dates */ INSERT INTO #DateTally ( Date ) SELECT TOP 10000 DATEADD(DAY, n, '20100101') FROM Nums /* Sample UK holiday */ INSERT INTO #DateHoliday ( Date ) VALUES ( '2011-01-03' ) , ( '2011-04-22' ) , ( '2011-04-25' ) , ( '2011-05-02' ) , ( '2011-05-30' ) , ( '2011-08-29' ) , ( '2011-12-26' ) , ( '2011-12-27' ) , ( '2012-01-02' ) , ( '2012-04-06' ) , ( '2012-04-09' ) , ( '2012-05-07' ) , ( '2012-05-28' ) , ( '2012-08-27' ) , ( '2012-12-25' ) , ( '2012-12-26' ) , ( '2013-01-01' ) , ( '2013-03-29' ) , ( '2013-04-01' ) , ( '2013-05-06' ) , ( '2013-05-27' ) , ( '2013-08-26' ) , ( '2013-12-25' ) , ( '2013-12-26' ) , ( '2014-01-01' ) , ( '2014-04-18' ) , ( '2014-04-21' ) , ( '2014-05-05' ) , ( '2014-05-26' ) , ( '2014-08-25' ) , ( '2014-12-25' ) , ( '2014-12-26' ) , ( '2015-01-01' ) , ( '2015-04-03' ) , ( '2015-04-06' ) , ( '2015-05-04' ) , ( '2015-05-25' ) , ( '2015-08-31' ) , ( '2015-12-25' ) , ( '2015-12-28' ) , ( '2016-01-01' ) , ( '2016-03-25' ) , ( '2016-03-28' ) , ( '2016-05-02' ) , ( '2016-05-30' ) , ( '2016-08-29' ) , ( '2016-12-26' ) , ( '2016-12-27' ) , ( '2017-01-02' ) , ( '2017-04-14' ) , ( '2017-04-17' ) , ( '2017-05-01' ) , ( '2017-05-29' ) , ( '2017-08-28' ) , ( '2017-12-25' ) , ( '2017-12-26' ) , ( '2018-01-01' ) , ( '2018-03-30' ) , ( '2018-04-02' ) , ( '2018-05-07' ) , ( '2018-05-28' ) , ( '2018-08-27' ) , ( '2018-12-25' ) , ( '2018-12-26' ) , ( '2019-01-01' ) , ( '2019-04-19' ) , ( '2019-04-22' ) , ( '2019-05-06' ) , ( '2019-05-27' ) , ( '2019-08-26' ) , ( '2019-12-25' ) , ( '2019-12-26' ) , ( '2020-01-01' ) , ( '2020-04-10' ) , ( '2020-04-13' ) , ( '2020-05-04' ) , ( '2020-05-25' ) , ( '2020-08-31' ) , ( '2020-12-25' ) , ( '2020-12-28' ) /* Update the IsHoliday flag */ UPDATE T SET T.IsHoliday = 1 FROM #DateTally T INNER JOIN #DateHoliday H ON T.Date = H.Date /* Sample dates (some with bank holidays in them) */ INSERT INTO #DateExample ( Date ) VALUES ( '2015-01-05' ) , ( '2015-04-04' ) , ( '2015-04-09' ) , ( '2015-05-05' ) , ( '2015-05-26' ) , ( '2015-03-26' ) , ( '2015-01-26' ) , ( '2015-09-26' ) /* Complete example */ SELECT E.Date AS 'DateOriginal' , DATEADD(DAY, -7, E.Date) AS 'DateMinusSeven' , a.DateCount FROM #DateExample E CROSS APPLY ( SELECT COUNT(*) - SUM(CASE WHEN IsHoliday = 1 THEN 1 ELSE 0 END) DateCount FROM #DateTally T WHERE T.Date DATEADD(DAY, -7, E.Date) ) a