NetWorkDays Function in SQL SERVER 2012

Question

Is there a function in sql server 2012 which calculates only working days?

I have been searching but with no luck so far.

Thanks!

Possible duplicate of [Count work days between two dates](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates) — Panagiotis Kanavos, Aug 08 '16 at 11:47
Googling for "T-SQL work days" produced a lot of results. Have you tried anything? — Panagiotis Kanavos, Aug 08 '16 at 11:48

score 0 · Accepted Answer · answered Aug 08 '16 at 11:55

No, SQL Server doesn't have such functions, but you can use calendar table:

DECLARE @date_start date = '2016-01-01',
        @date_end date = '2016-12-31';

WITH cte as (
SELECT @date_start as [d], 0 as Level
UNION ALL
SELECT DATEADD(day,1,[d]), [level] + 1 as [level]
from cte
WHERE [level] < DATEDIFF(day,@date_start,@date_end)
),

holidays as ( --table with holidays (USA)
SELECT * FROM (VALUES
('2016-01-01'),
('2016-01-18'),
('2016-02-15'),
('2016-05-30'),
('2016-07-04'),
('2016-09-05'),
('2016-10-10'),
('2016-11-11'),
('2016-11-24'),
('2016-12-26')) as t(d)
)

SELECT  c.d,
        CASE WHEN DATEPART(WEEKDAY,c.d) IN (1,7) THEN 0 --Saturday and Sunday, use (6,7) for Friday,Saturday
            WHEN h.d IS NOT NULL THEN 0
            ELSE 1 END as isWorking
FROM cte c
LEFT JOIN holidays h 
    ON c.d=h.d
OPTION (MAXRECURSION 1000);

It will generate a table with all dates in 2016 year and flag - is the day working or not.

score 0 · Answer 2 · answered Aug 08 '16 at 11:58

Below is the high level overview of how you can do this..

Create a dummy table which holds dates in this format...

date     isholiday
20160101   1
20160102   0

Now from your main table which holds employees attendance ,join above table like..

select empid,sum(Case when mt.datee is not null then 1  else 0 end) as workingdays
from
dummydatees dt
left join 
maintable mt
on dt.datee=mt.datee
where dt.isholiday=0

score 0 · Answer 3 · edited Jun 18 '17 at 06:28

This script will calculate the total working days excluding Saturday, Sunday and holidays. I have to list all the holidays since I don't have a table for holidays. You can modify it so that it will meet your requirements.

DECLARE @MyCounter int = 0, @TempDate datetime,  @EndDate datetime;
SET @TempDate = DATEADD(d,1,'2017-5-27')
SET @EndDate = '2017-6-3'

WHILE @TempDate <= @EndDate
    BEGIN
    IF DATENAME(DW,@TempDate) = 'Sunday' OR DATENAME(DW,@TempDate) = 'Saturday'
        SET @MyCounter = @MyCounter
    ELSE IF @TempDate not in ('2017-1-1', '2017-1-16', '2017-2-20', '2017-5-29', '2017-7-4', '2017-9-4', '2017-10-9', '2017-11-11', '2017-12-25')
        SET @MyCounter = @MyCounter + 1

    SET @TempDate = DATEADD(d,1,@TempDate)
    CONTINUE

END
PRINT @MyCounter
PRINT @TempDate

NetWorkDays Function in SQL SERVER 2012

3 Answers3