Question: I have this table where I am specifying Holidays in week. I want to calculate total working days between 2 specific dates using these fields. 

```
CREATE TABLE [tbl_Shift](
[OffDay1] [nvarchar](25) NOT NULL CONSTRAINT [DF_tbl_Shift_OffDay1]  DEFAULT (N'Sunday'),
[IsAlternateOffDay2] [bit] NULL,
[OffDay2] [nvarchar](25) NULL
)

INSERT INTO [tbl_Shift] VALUES ('Sunday', 'True', 'Saturday')

```

I have this query written but I am not able to get correct days. It should give 23 days as there are 2 holidays in each week and 31 days in total but I'm getting 26 days.

```
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2018/05/01'
SET @EndDate = '2018/05/31'

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate+1)) -(DATEDIFF(wk, @StartDate, @EndDate))
   -(CASE WHEN IsAlternateOffday2 = 1 THEN 1 END) FROM HRM.tbl_Shift

```

Answer: 
Try This

```
DECLARE @StartDate DATE ='2018-05-01',
        @EndDate DATE ='2018-05-31'
;WITH CTE
AS
(
SELECT DATEADD(DD,Number-1,@StartDate) MOnthDates,
       DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) As DayNAmes,
       CASE WHEN DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) IN ('Saturday','Sunday') THEN 0 ELSE 1 END WeekDays
FROM master.dbo.spt_values
WHERE [Type]='P'
AND Number Between 1 AND 10000
)
SELECT COUNT(WeekDays)  AS WeekDaysCount
FROM CTE
WHERE WeekDays<>0
AND MOnthDates Between @StartDate AND DATEADD(DAY,1,@EndDate)

```

Result

```
WeekDaysCount
-------------
23

```

Demo:<http://rextester.com/TOLYT35075>
This was what I created after reviewing the answers. I needed the number of days per month for several months. The below will provide that, and insert it into the `#MonthDayCount` table. Note: I named a field `Month`, even though that is a SQL Server defined term; you can change it if needed.

You must enter the first day of the month and the last for it to count the first and last month correctly. If you entered `1/2/2022`, it would result in January being short one day.

```
DECLARE @StartDate DATE = '1/01/2021'--The start of the first month the number of days are needed for.
DECLARE @EndDate DATE = '1/31/2021'--The end of the first month the number of days are needed for.
DECLARE @FinalDate DATE = '12/31/2022'--This is the last month that will be inserted. Includes this month.

IF OBJECT_ID('tempdb..#MonthDayCount', 'U') IS NOT NULL
DROP TABLE #MonthDayCount; 
CREATE TABLE #MonthDayCount
                            (
                                [Month]             VARCHAR(MAX),
                                [# of WeekDays]     INT,
                                [EOM]               DATE
                            )

WHILE           @StartDate <= @FinalDate
BEGIN 
INSERT INTO     
    #MonthDayCount
                    VALUES  (
                                DATENAME(m,@StartDate),
                                (
                                    DATEDIFF(dd, @StartDate, @EndDate) + 1)
                                    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
                                    -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
                                    -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END
                                ),
                                @EndDate
                            )

  SET @StartDate = DATEADD(m,1,@StartDate)
  SET @EndDate = EOMONTH(@EndDate,1)

END  

```