Consecutive count of holidays if first date of a month is holiday

Transact-SQL
1

Hello

Table Name temp2025
Columns:
dt , contains all dates of the year
gz_holiday, Y/N
yearmonth, year and month of the year

What I Want:

If first date of a month gz_holiday 'Y' then count consecutive holidays from first date of the month.
Note in May first date is holiday and 28 may is also holiday but since they are not consecutive hence the count is 1.


Result 
yearmonth holiday_count
---------------------
202501		0
202502		0
202503		0
202504		2
202505		1
202506		0

/*
CREATE TABLE [dbo].[temp2025](
	[dt] [smalldatetime] NOT NULL,
	[gz_holiday] [varchar](1) NULL,
	[yearmonth] [int] NULL,
 CONSTRAINT [PK_temp2025] PRIMARY KEY CLUSTERED 
(
	[dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]




INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  1 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  2 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  3 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  4 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  5 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  6 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  7 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  8 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan  9 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 10 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 11 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 12 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 13 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 14 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 15 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 16 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 17 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 18 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 19 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 20 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 21 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 22 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 23 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 24 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 25 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 26 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 27 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 28 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 29 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 30 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jan 31 2025 12:00:00:000AM','N',202501)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  1 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  2 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  3 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  4 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  5 2025 12:00:00:000AM','Y',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  6 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  7 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  8 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb  9 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 10 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 11 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 12 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 13 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 14 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 15 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 16 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 17 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 18 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 19 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 20 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 21 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 22 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 23 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 24 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 25 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 26 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 27 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Feb 28 2025 12:00:00:000AM','N',202502)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  1 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  2 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  3 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  4 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  5 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  6 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  7 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  8 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar  9 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 10 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 11 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 12 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 13 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 14 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 15 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 16 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 17 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 18 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 19 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 20 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 21 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 22 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 23 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 24 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 25 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 26 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 27 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 28 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 29 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 30 2025 12:00:00:000AM','N',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Mar 31 2025 12:00:00:000AM','Y',202503)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  1 2025 12:00:00:000AM','Y',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  2 2025 12:00:00:000AM','Y',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  3 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  4 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  5 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  6 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  7 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  8 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr  9 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 10 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 11 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 12 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 13 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 14 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 15 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 16 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 17 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 18 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 19 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 20 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 21 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 22 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 23 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 24 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 25 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 26 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 27 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 28 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 29 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Apr 30 2025 12:00:00:000AM','N',202504)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  1 2025 12:00:00:000AM','Y',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  2 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  3 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  4 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  5 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  6 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  7 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  8 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May  9 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 10 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 11 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 12 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 13 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 14 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 15 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 16 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 17 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 18 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 19 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 20 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 21 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 22 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 23 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 24 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 25 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 26 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 27 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 28 2025 12:00:00:000AM','Y',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 29 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 30 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('May 31 2025 12:00:00:000AM','N',202505)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  1 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  2 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  3 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  4 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  5 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  6 2025 12:00:00:000AM','Y',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  7 2025 12:00:00:000AM','Y',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  8 2025 12:00:00:000AM','Y',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun  9 2025 12:00:00:000AM','Y',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 10 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 11 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 12 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 13 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 14 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 15 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 16 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 17 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 18 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 19 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 20 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 21 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 22 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 23 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 24 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 25 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 26 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 27 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 28 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 29 2025 12:00:00:000AM','N',202506)
INSERT INTO [temp2025] ([dt],[gz_holiday],[yearmonth])VALUES('Jun 30 2025 12:00:00:000AM','N',202506)
2

This is an islands and gaps problem so something like:

WITH yearmonths
AS
(
    /* This should be a separate table */
    SELECT DISTINCT yearmonth
    FROM dbo.temp2025
)
,Boundaries
AS
(
    SELECT dt, gz_holiday, yearmonth
        ,IIF(LAG(gz_holiday, 1) OVER (ORDER BY dt) != gz_holiday, 1, 0) AS Boundary
    from dbo.temp2025
)
,Grps
AS
(
    SELECT dt, gz_holiday, yearmonth
        ,SUM(Boundary) OVER (ORDER BY dt) AS Grp
    FROM Boundaries
)
,GrpCounts
AS
(
    SELECT dt, gz_holiday, yearmonth
        ,COUNT(*) OVER (PARTITION BY yearmonth, Grp) AS GrpCount
    FROM Grps
)
SELECT Y.yearmonth, COALESCE(G.GrpCount , 0) AS holiday_count
FROM yearmonths Y
    LEFT JOIN GrpCounts G
        ON Y.yearmonth = G.yearmonth
            AND DAY(G.dt) = 1
                AND G.gz_holiday = 'Y'
ORDER BY yearmonth;
1 Like
3

I would say find the first date in the month that is NOT a holiday, subtract 1 from that day, and that's how many leading holidays there are. Something like this:


;WITH cte_first_non_holidays AS (
    SELECT yearmonth, 
        MIN(CASE WHEN gz_holiday = 'N' THEN dt 
                 ELSE DATEDIFF(MONTH, 0, dt) + 1 , 0) END) AS first_non_holiday
    FROM [temp2025]
    GROUP BY yearmonth
)
SELECT yearmonth, DATEPART(DAY, first_non_holiday) - 1
FROM cte_first_non_holidays 
ORDER BY yearmonth
2 Likes
4

Hello

5

@ScottPletcher
Your idea is appealing. In some units our month goes from 26th to 25th your code will work in that case too.
Unfortunately there is syntax error in your code i could not figure out

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.

6

Your solution is correct and giving correct result.

7

Scott's approach is better:

WITH cte_first_non_holidays
AS
(
    SELECT yearmonth
        ,MIN(IIF(gz_holiday = 'N', dt, NULL)) AS first_non_holiday
    FROM #t
    GROUP BY yearmonth
)
SELECT yearmonth, DATEPART(DAY, first_non_holiday) - 1 AS holiday_count
FROM cte_first_non_holidays 
ORDER BY yearmonth;
1 Like
8

Thanks

9

Hmm, I think that would require some adjustment. I (mistakenly) assumed a calendar month (in a bit of hurry when I did the code).

To allow for the yearmonth to start on any day, get both the "absolute" MIN() and the "N" MIN(), then get the day diff between the two:


;WITH cte_first_non_holidays AS (
    SELECT yearmonth, MIN(dt) AS first_day,
        MIN(CASE WHEN gz_holiday = 'N' THEN dt ELSE NULL END) AS first_non_holiday
    FROM dbo.[temp2025]
    GROUP BY yearmonth
)
SELECT yearmonth, DATEDIFF(DAY, first_day, first_non_holiday) AS holiday_count
FROM cte_first_non_holidays 
ORDER BY yearmonth
2 Likes
10

@ScottPletcher
Working like magic.

Many many thanks.

11

You're welcome, but please change the "Solution" to the correct code, for people potentially using this q later.

12

Solution changed to correct code.

13

hi

this is another way of doing it

am doing it for my "noodles"

how you take it is up to you

:stuck_out_tongue_winking_eye: thanks


select
     yearmonth
	 , datediff(dd, min(iif(gz_holiday = 'Y' and day(dt) = 1 , dt,null)), min(iif(gz_holiday = 'N' and day(dt) <> 1 , dt,null))	 )
from 
  dbo.temp2025 
group by 
     yearmonth

image
image311×212 16.8 KB

1 Like