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: 
this will give 23:

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

```
This subtracts 2 days for every weekend. Check your server config if Sunday is first or last day of the week. This can throw things off by a weekend.

```
SELECT DATEPART(WEEKDAY,'20180506') --Checks if Sunday is Day 1 or Day 7

DECLARE @start DATETIME = '20180501'
DECLARE @end DATETIME = '20180531'

SELECT DATEDIFF(DAY,@start,@end+1) - (DATEDIFF(WEEK,@start,@end+1)*2)

```

UPDATE:

Use COALESCE to replace NULLS with an alternative value.

```
DECLARE @offdate DATETIME = NULL

SELECT COALESCE(@offdate,GETDATE())

```