To check initial dates of each salary month for holiday

Transact-SQL
1

I have sql server 2008

I have a table alldates3 with columns dt means dates
, isholiday means if a date is holiday or not
, salarymonth means the dates contain to which salary month
our salarymonths goes from 26th to 25th.
I think the sample data provided may be easy to understand.

We need to find holiday_count (where isholiday='Y')
but we need to check first four dates of each salarymonth
that is 26th, 27th, 28th, 29th (in some february there may not be 29th)

IF first date of a salarymonth is not holiday then holiday count is 0
else
if 1st,second,3rd dates of a salarymonth are holidays then holiday count is 3
else
if 1st,second dates of a salarymonth are holidays then holiday count is 2
else
if 1st date of a salarymonth is holiday then holiday count is 1

to state briefly
If first date is not holiday no issue holiday count 0

if first date is holiday then we need to go on counting till there is non holiday

There is and will never be gap in dates.

the expected result with sample table and data would be

salarymonth holiday_count
202001 0
202002 1
202003 0
202004 0
202005 1
202006 2

Is simple meaningful query possible instead of doing in a store proc by looping?

Here is sample schema and data

CREATE TABLE [dbo].[alldates3](
	[dt] [date] NOT NULL,
	[isholiday] [varchar](1) NULL,
	[holiday_name] [varchar](50) NULL,
	[salarymonth] [bigint] NULL,
 CONSTRAINT [PK_alldates3] 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 [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2019-12-26','N','Thursday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2019-12-27','N','Friday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2019-12-28','N','Saturday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2019-12-29','Y','Sunday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2019-12-30','N','Monday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2019-12-31','N','Tuesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-01','N','Wednesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-02','N','Thursday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-03','N','Friday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-04','N','Saturday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-05','Y','Sunday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-06','N','Monday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-07','N','Tuesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-08','N','Wednesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-09','N','Thursday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-10','N','Friday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-11','N','Saturday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-12','Y','Sunday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-13','N','Monday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-14','N','Tuesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-15','N','Wednesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-16','N','Thursday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-17','N','Friday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-18','N','Saturday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-19','Y','Sunday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-20','N','Monday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-21','N','Tuesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-22','N','Wednesday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-23','N','Thursday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-24','N','Friday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-25','N','Saturday',202001)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-26','Y','Sunday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-27','N','Monday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-28','N','Tuesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-29','N','Wednesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-30','N','Thursday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-01-31','N','Friday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-01','N','Saturday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-02','Y','Sunday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-03','N','Monday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-04','N','Tuesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-05','Y','Kashmir Day',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-06','N','Thursday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-07','N','Friday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-08','N','Saturday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-09','Y','Sunday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-10','N','Monday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-11','N','Tuesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-12','N','Wednesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-13','N','Thursday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-14','N','Friday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-15','N','Saturday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-16','Y','Sunday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-17','N','Monday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-18','N','Tuesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-19','N','Wednesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-20','N','Thursday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-21','N','Friday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-22','N','Saturday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-23','Y','Sunday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-24','N','Monday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-25','N','Tuesday',202002)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-26','N','Wednesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-27','N','Thursday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-28','N','Friday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-02-29','N','Saturday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-01','Y','Sunday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-02','N','Monday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-03','N','Tuesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-04','N','Wednesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-05','N','Thursday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-06','N','Friday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-07','N','Saturday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-08','Y','Sunday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-09','N','Monday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-10','N','Tuesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-11','N','Wednesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-12','N','Thursday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-13','N','Friday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-14','N','Saturday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-15','Y','Sunday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-16','N','Monday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-17','N','Tuesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-18','N','Wednesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-19','N','Thursday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-20','N','Friday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-21','N','Saturday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-22','Y','Sunday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-23','Y','Pakistan Day',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-24','N','Tuesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-25','N','Wednesday',202003)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-26','N','Thursday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-27','N','Friday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-28','N','Saturday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-29','Y','Sunday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-30','N','Monday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-03-31','N','Tuesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-01','N','Wednesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-02','N','Thursday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-03','N','Friday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-04','N','Saturday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-05','Y','Sunday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-06','N','Monday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-07','N','Tuesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-08','N','Wednesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-09','N','Thursday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-10','N','Friday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-11','N','Saturday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-12','Y','Sunday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-13','N','Monday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-14','N','Tuesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-15','N','Wednesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-16','N','Thursday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-17','N','Friday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-18','N','Saturday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-19','Y','Sunday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-20','N','Monday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-21','N','Tuesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-22','N','Wednesday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-23','N','Thursday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-24','N','Friday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-25','N','Saturday',202004)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-26','Y','Sunday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-27','N','Monday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-28','N','Tuesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-29','N','Wednesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-04-30','N','Thursday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-01','Y','Labour Day',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-02','N','Saturday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-03','Y','Sunday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-04','N','Monday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-05','N','Tuesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-06','N','Wednesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-07','N','Thursday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-08','N','Friday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-09','N','Saturday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-10','Y','Sunday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-11','N','Monday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-12','N','Tuesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-13','N','Wednesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-14','N','Thursday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-15','N','Friday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-16','N','Saturday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-17','Y','Sunday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-18','N','Monday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-19','N','Tuesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-20','N','Wednesday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-21','N','Thursday',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-22','Y','Eid al-Fitr',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-23','Y','Eid al-Fitr',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-24','Y','Eid al-Fitr',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-25','Y','Eid al-Fitr',202005)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-26','Y','Eid al-Fitr',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-27','Y','Eid al-Fitr',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-28','N','Thursday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-29','N','Friday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-30','N','Saturday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-05-31','Y','Sunday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-01','N','Monday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-02','N','Tuesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-03','N','Wednesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-04','N','Thursday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-05','N','Friday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-06','N','Saturday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-07','Y','Sunday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-08','N','Monday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-09','N','Tuesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-10','N','Wednesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-11','N','Thursday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-12','N','Friday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-13','N','Saturday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-14','Y','Sunday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-15','N','Monday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-16','N','Tuesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-17','N','Wednesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-18','N','Thursday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-19','N','Friday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-20','N','Saturday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-21','Y','Sunday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-22','N','Monday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-23','N','Tuesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-24','N','Wednesday',202006)
INSERT INTO [alldates3] ([dt],[isholiday],[holiday_name],[salarymonth])VALUES('2020-06-25','N','Thursday',202006)
2

Maybe:

WITH LEADs
AS
(
	SELECT dt, salarymonth, isholiday
		,LEAD(isholiday) OVER (ORDER BY dt) AS isholiday1
		,LEAD(isholiday, 2) OVER (ORDER BY dt) AS isholiday2
		,COALESCE(LEAD(isholiday, 3) OVER (ORDER BY dt), 'N') AS isholiday3
	FROM [dbo].[alldates3]
	WHERE DAY(dt) BETWEEN 26 AND 29
)
SELECT salarymonth
	,CASE
		WHEN isholiday = 'Y'
			AND isholiday1 = 'Y'
			AND isholiday2 = 'Y'
			AND isholiday3 = 'Y'
		THEN 4
		WHEN isholiday = 'Y'
			AND isholiday1 = 'Y'
			AND isholiday2 = 'Y'
		THEN 3
		WHEN isholiday = 'Y'
			AND isholiday1 = 'Y'
		THEN 2
		WHEN isholiday = 'Y'
		THEN 1
		ELSE 0
	END AS holiday_count
FROM LEADs
WHERE DAY(dt) = 26;
1 Like
3

@Ifor

I have checked in sql server 2012

Your query is working correctly and I just want this.

In fact I have edited my expected result above after seeing the result of your query.

But I want the same in Sql server 2008 as i mentioned in my question.

Thanks for your reply

4

hi

i tried to do this ..

this is a different way .. hope this helps ..

its one way of Many different approaches ..

; with cte as 
(
	select * from alldates3 where day(dt) = 26 and isholiday = 'Y' 
), 
   cte_rn as 
(
	select   ROW_NUMBER() OVER (PARTITION BY a.salarymonth ORDER BY a.dt ) AS rn
	       , a.* 
    from alldates3 a join cte b on a.salarymonth = b.salarymonth
) ,
   cte_cnt as 
(
	select *,
	       sum(case when isholiday = 'Y' then 1 else 0 end ) over(partition by salarymonth order by dt) as sumx
	from cte_rn where rn in(1,2,3,4)
)
select salarymonth,max(sumx) from cte_cnt group by salarymonth

image

1 Like
5

With SQL2008 you are left with two inner joins and one left outer join.

Given that SQL2008 is out of extended support and there were some long term security fixes in January you may want to look at upgrading to at least SQL2012 ASAP.

1 Like
6

Agreed.

7

@harishgg1

As suggested by @ifor I must ask my boss to for 2012 or 2014,
as your solution also uses sum() with over clause not applicable in 2008

8

Have you tried using a Case/When with a Max?

;with cteMonth as 
  (SELECT  salarymonth, 
		max(Case when  DAY(dt) = 26 then IsHoliday end) as Day26,
		max(Case when  DAY(dt) = 27 then IsHoliday end) as Day27,
		max(Case when  DAY(dt) = 28 then IsHoliday end) as Day28,
		max(Case when  DAY(dt) = 29 then IsHoliday end) as Day29
	FROM [dbo].[alldates3]
	Group BY salarymonth)

select a.*
		, case when c.Day26 = 'N' then 0
			   when c.Day26 = 'Y' and IsNull(c.Day27,'N') = 'N' then 1
			   when c.Day26 = 'Y' and c.Day27 = 'Y' and IsNull(c.Day28,'N') = 'N' then 2
			   when c.Day26 = 'Y' and c.Day27 = 'Y' and c.Day28 = 'Y'  and IsNull(c.Day29,'N') = 'N' then 3
			   when c.Day26 = 'Y' and c.Day27 = 'Y' and c.Day28 = 'Y'  and c.Day29 = 'Y' then 4
			end as holiday_Count
  from [alldates3] a
  join cteMonth c
	on a.salarymonth = c.salarymonth
1 Like
9 
SELECT a1.salarymonth, 
    CASE WHEN a1.isholiday = 'N' THEN 0 
         ELSE 1 + CHARINDEX('N', ca1.holidays + 'N') - 1 END AS holiday_count
FROM dbo.alldates3 a1
OUTER APPLY (
    SELECT 
        (SELECT isholiday FROM dbo.alldates3 WHERE dt = DATEADD(DAY, 1, a1.dt)) +
        (SELECT isholiday FROM dbo.alldates3 WHERE dt = DATEADD(DAY, 2, a1.dt)) +
        (SELECT isholiday FROM dbo.alldates3 WHERE dt = DATEADD(DAY, 3, a1.dt)) 
            AS holidays
    WHERE a1.isholiday = 'Y'
) AS ca1 
WHERE DAY(a1.dt) = 26
1 Like
10

yes it worked

11

Yes
It seems to me brief and elegant.
It worked.

Thanks

12

@ScottPletcher
as @JeffModen often say, do you understand the solution.

Experts already know, in case any one like me interested.

CASE WHEN a1.isholiday = 'N' THEN 0 **
** ELSE 1 + CHARINDEX('N', ca1.holidays + 'N') - 1 END AS holiday_count

is to avoid deeply nested case when construct.

WHEN a1.isholiday = 'N' , result of the main query
then straight forward 0 as holiday count
else first add 1 (because then a1.isholiday='y')
then look into concatenation of outer apply code
which may be something like
'NYY' OR 'YNY' OR 'YYY' etc
add a suffix 'N' and find the nth position of 'N' in concatenated string
and finally subtract 1 because we added 'N' to the concatenation

13

first add 1 (because then a1.isholiday='y')
Exactly right!

finally subtract 1 because we added 'N' to the concatenation
Close but not exact,
Subtract 1 because we're finding the position of the first N, but we need the count of the leading Ys coming before the first N.
Some examples:
NYYN --first N is found in position 1, which means no leading holiday days, 1 - 1 = 0, no holidays added (total of 1)
YNYN -- first N is in position 2, 2 - 1 = 1 holiday added = (total of 2)
YYYN -- first N is in position 4, 4 - 1 = 3 holidays added = (total of 4, max)

I had to add the 'N' to handle the possibility that all three of the next days are holidays. If I don't, then:
YYY
the first N is in position 0 (not found!), 0 - 1 = -1 = (0 total) would be big bug in the code!

14

so technically anyone could add any string value to isHoliday. Should that column be bit data type? and avoid the string manipulation circus downstream

15

I had to add the 'N' to handle the possibility that all three of the next days are holidays

Yes off course.

Its a brilliant logic and can handle more than four case of holidays
(My sample data has 6 continuous holidays from 22 May 2020 to 27 May 2020
though falling in different months)
by just adding appropriate line in the outer apply clause.

16

@yosiasz

isholiday, islocked etc should be bit data type.

I agree

17

or create a constraint for only N and Y but even then if other applications use those columns now they have to convert N to false and Y to true etc.

18

No, one should NOT be able to add "any string" to isHoliday, because technically it should have a CHECK condition that allows only 'N' and 'Y'. I presumed such a thing existed.

As to bit, it depends on its specific usage AND the amount of historical code that would have to be changed just to be able to use a bit. There's not all that big a diff between a bit and N/Y.

2 Likes
19

If it were a bit, I'd have used strings of '0' and '1', which functions exactly the same way.

I, too, think my approach is briefer and more elegant -- and clearer to read -- than other solutions here, including LEAD / LAG (which aren't available in SQL 2008 anyway). Such added functions are great when they are needed, but I don't think they're genuinely needed here, and so they add overhead and complexity for no value.

Note that the WHERE condition prevents the forward reads from even occurring if the current day is not a holiday. You will see that the "holidays" column comes back NULL if the a1.isHoliday = 'N'.

1 Like
20

agree with all you say except when this spills out of the SQL Server world, it will be an issue, believe me. But the change to bit might cause even more issues like you said