How can I calculate total days excluding weekends?

Question

I have a SQL query which calculates how many days an employee has worked based on their hire date (anniversary). However, these days include weekends, so I need exclude Saturdays and Sundays. I tried to use DATEADD(dw, ...) but it did not work.

SELECT PRCo, Employee,LastName, FirstName, MidName,SSN,HireDate,
(case when 
      DATEADD(year, datediff(year, HireDate, getdate()), HireDate) < GETDATE()
 then DATEDIFF(dd, DATEADD(year, datediff(year, HireDate, getdate()), HireDate), getdate())
 else DATEDIFF(dd, DATEADD(year, datediff(year, HireDate, getdate()) - 1, HireDate),
               getdate()) end) as HRSDATE
FROM dbo.PREH

calculate number of weeks between hiredate and curdate, then minus 2 * no_of weeks he has worked — Krishna Rani Sahoo, Jul 17 '13 at 14:07
Create a Calendar table, and you can exclude weekends and holidays. http://www.brianshowalter.com/calendar_tables — Gilbert Le Blanc, Jul 17 '13 at 14:13
@user2587986 please check this [link](http://stackoverflow.com/a/7388873/1563878) — Gaston Flores, Jul 17 '13 at 14:43

score 1 · Answer 1 · answered Jul 17 '13 at 14:09

1

A simple way of accomplishing this is to create a calendar table, with the date, day of week, and day of year all loaded into the table. Then you simply select days since the employee's start date where day of week != Saturday or Sunday.

answered Jul 17 '13 at 14:09

Chris

2,885
18
25

Thanks. I will try it when i have time. Now i just simply subtract 2 * number of weeks from the result? – user2587986 Jul 17 '13 at 15:25

score 1 · Answer 2 · answered Jul 17 '13 at 15:11

1

Just subtract 2 * number of weeks from the result?

answered Jul 17 '13 at 15:11

This will require a little bit of care in terms of start/end exclusion: working monday(1)-friday(5) (5 days) needs to count a zero weeks, while friday(5)-monday(8) (4 days) needs to still count as one. EDIT: Subtracting weeknumber appears to give the correct behaviour. – zebediah49 Jul 17 '13 at 15:15
Yes, it works. Maybe later on i try to set the calendar table but for now that is. thanks – user2587986 Jul 17 '13 at 15:24

score 0 · Answer 3 · edited May 23 '17 at 10:25

With a suitable calendar table, you'd probably write something like this. (Assuming the hire date is 2011-01-15.) One of the biggest advantages of a calendar table is that code can be seen to be obviously correct.

select count(*) as num_weekdays
from calendar
where cal_date between '2011-01-15' and current_date
  and day_of_week not in ('Sat', 'Sun');

Even simpler, if you create a view named "weekdays" that includes only weekdays . . .

select count(*) as num_weekdays
from weekdays
where cal_date between '2011-01-15' and current_date;

This SO answer has PostgreSQL code for a calendar table. You can adapt it pretty easily for SQL Server.

Thanks.Since this report just ask for the rough data, i just simply subtract 2*number of weeks. but I think in the future calendar table is what i need. — user2587986, Jul 17 '13 at 15:23

score 0 · Answer 4 · edited Oct 16 '14 at 22:59

IF it needs to be accurate,
STEP01 SUBTRACT 2 times the number integer (DATEDIFF RESULT) divided by 7
STEP02 THEN take 7 minus the week day (START DATE) number (USE DATEPART)
STEP03 AND if it is bigger than the number (DATEDIFF RESULT) , subtract 2.

DECLARE @start AS datetime = '10/24/2014'

DECLARE @end AS datetime = '10/31/2014'
SELECT CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS int)AS STEP_ONE_OrigDays
--,CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money)*(1.000*(5.000/7.000)) AS EstDays
,(2.000*(CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money)/7)) AS STEP_TWO--SUBTRACT 2 times the number integer divided by 7
,DATEPART(dw, @start) AS STEP_THREE_WEEK_DAY_NUMBER --WEEK DAY #
,7 - DATEPART(dw, @start) AS STEP_FOUR_SEVENminus_WEEK_DAY_NUMBER --7 minus WEEK DAY #
,CAST(CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money)
-(2.000*(CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money)/7))
+CASE 
WHEN (7.000-DATEPART(dw, @start)) > CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money) 
THEN -2.000
ELSE 0.000 
END AS int) AS ExactDays_NOT_IncludingDayOf --JAH EXCLUDE WEEKEND Fx NOT Including the Day of
,CAST(CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money)-(2.000*(CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money)/7))
+CASE 
WHEN (7.000-DATEPART(dw, @start)) > CAST(DATEDIFF("d",CAST(@start AS datetime),CAST(@end AS datetime)) AS money) 
THEN -2.000
ELSE 0.000 
END AS int)+1 AS ExactDays_IncludingDayOf --JAH EXCLUDE WEEKEND Fx INCLUDING the Day of

Welcome to Stack Overflow. Please take a moment to review the [tour] and visit the [help]. Please edit your answer to include an explanation of that this code is doing, and please take a look at the help link to the bottom right of the editor window for assistance formatting your post. — Tritium21, Oct 16 '14 at 22:48

How can I calculate total days excluding weekends?

4 Answers4