T-SQL Get Number of Working Days Between 2 Dates

Count work days between two dates

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'

SELECT
   (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)

If you want to include holidays, you have to work it out a bit...

T-SQL get number of working days between 2 dates

Please, please, please use a calendar table. SQL Server doesn't know anything about national holidays, company events, natural disasters, etc. A calendar table is fairly easy to build, takes an extremely small amount of space, and will be in memory if it is referenced enough.

Here is an example that creates a calendar table with 30 years of dates (2000 -> 2029) but requires only 200 KB on disk (136 KB if you use page compression). That is almost guaranteed to be less than the memory grant required to process some CTE or other set at runtime.

CREATE TABLE dbo.Calendar
(
  dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008
  IsWorkDay BIT
);

DECLARE @s DATE, @e DATE;
SELECT @s = '2000-01-01' , @e = '2029-12-31';

INSERT dbo.Calendar(dt, IsWorkDay)
  SELECT DATEADD(DAY, n-1, '2000-01-01'), 1 
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER() 
      OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
  ) AS x(n);

SET DATEFIRST 1;

-- weekends
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE DATEPART(WEEKDAY, dt) IN (6,7);

-- Christmas
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE MONTH(dt) = 12
  AND DAY(dt) = 25
  AND IsWorkDay = 1;

-- continue with other holidays, known company events, etc.

Now the query you're after is quite simple to write:

SELECT COUNT(*) FROM dbo.Calendar
  WHERE dt >= '20130110'
    AND dt <  '20130115'
    AND IsWorkDay = 1;

More info on calendar tables:

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

More info on generating sets without loops:

http://www.sqlperformance.com/tag/date-ranges

Also beware of little things like relying on the English output of DATENAME. I've seen several applications break because some users had a different language setting, and if you're relying on WEEKDAY be sure you set your DATEFIRST setting appropriately...

SQL Date Diff Business Days

You could change your count to a conditional sum, using DATEPART to ignore the Saturdays and Sundays (which should have a dw of 7 & 1 respectively).

SELECT SUM(CASE WHEN DATEPART(dw, COALESCE(Date1, Date2)) BETWEEN 2 AND 6 
           THEN 1 ELSE 0 END)
FROM Orders
WHERE DATEDIFF(dd,COALESCE(Date1, Date2),Date0) <= 1

SQL to get number of working days between two dates

The last line removes the partial weeks, which only happens when the start date falls on a Sunday and the last date falls on a Saturday.

The second line,(DATEDIFF(wk, '2015-01-04', '2015-01-10') * 2), counts whole weekends, that is a Saturday-Sunday pair. So in your question, if the first date is a Saturday , then it will count as one Saturday-Sunday pair. Same will be applied if the last date falls on a Sunday.

Calculate total business working days between two dates

If you only want to exclude weekends then you can simply just exclude these using a conditional count by adding:

count(distinct case when datepart(weekday, getdate()) <= 5 then date end)

So your query becomes:

set datefirst 1;

select  count(distinct(dateadd(d, 0, datediff(d, 0,checktime)))) as workingdays,
        count(distinct case when datepart(weekday, getdate()) <= 5 
                            then dateadd(d, 0, datediff(d, 0,checktime)) 
                        end) as weekdays
from departments,
 dbo.USERINFO INNER JOIN dbo.CHECKINOUT ON 
     dbo.USERINFO.USERID = dbo.CHECKINOUT.USERID
where  userinfo.name='Gokul Gopalakrishnan' and deptname='GEN/SUP-TBL' 
and checktime>='2014-05-01' and checktime<='2014-05-30'

HOWEVER I would really recommend adding a calendar table to your database. It makes everything so easy, your query would become:

SELECT  DaysWorked = COUNT(cio.Date),
        WeekDaysWorked = COUNT(CASE WHEN c.IsWeekDay = 1 THEN cio.Date END),
        WorkingDaysWorked = COUNT(CASE WHEN c.IsWorkingDay = 1 THEN cio.Date END),
        TotalDays = COUNT(*),
        TotalWeekDays = COUNT(CASE WHEN c.IsWeekDay = 1 THEN 1 END),
        TotalWorkingDays = COUNT(CASE WHEN c.IsWorkingDay = 1 THEN 1 END)
FROM    dbo.Calender AS c
        LEFT JOIN
        (   SELECT  DISTINCT
                    Date = CAST(CheckTime AS DATE)
            FROM    dbo.Departments AS d
                    CROSS JOIN dbo.userInfo AS ui
                    INNER JOIN dbo.CheckInOut AS cio
                        ON cio.UserID = ui.UserID
            WHERE   ui.Name = 'Gokul Gopalakrishnan' 
            AND     d.deptname = 'GEN/SUP-TBL' 
        ) AS cio
            ON c.Date = cio.Date
WHERE   d.Date >= '2014-05-01'
AND     d.Date <= '2014-05-30';

This way you can define public holidays, weekends, etc. It is so much more flexible than any other solution.

EDIT

I think I misunderstood your original criteria. This should work for you with no calendar table:

SET DATEFIRST 1;

DECLARE @StartDate DATE = '2014-05-01', 
        @EndDate DATE = '2014-05-30';

DECLARE @Workdays INT = 
    (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
    -(DATEDIFF(WEEK, @StartDate, @EndDate) * 2)
    -(CASE WHEN DATEPART(WEEKDAY, @StartDate) = 7 THEN 1 ELSE 0 END)
    -(CASE WHEN DATEPART(WEEKDAY, @EndDate) = 6 THEN 1 ELSE 0 END);

SELECT  WorkingDays = COUNT(DISTINCT CAST(CheckTime AS DATE)),
        BusinessDays = @Workdays
FROM    dbo.Departments AS d
        CROSS JOIN dbo.userInfo AS ui
        INNER JOIN dbo.CheckInOut AS cio
            ON cio.UserID = ui.UserID
WHERE   ui.Name = 'Gokul Gopalakrishnan' 
AND     d.deptname = 'GEN/SUP-TBL' 
AND     cio.CheckTime >= @StartDate
AND     cio.CheckTime <= @EndDate;

How to count only the working days between two dates?

After you have created a table that stores the holiday dates, then you probably can do something like this:

SELECT id_employee, 
       EXTRACT(YEAR FROM t.Date) AS year, 
       EXTRACT(MONTH FROM t.Date) AS month, 
       SUM(CASE WHEN h.holiday_date IS NULL THEN WEEKDAY(`Date`) < 5 END) AS days 
FROM (SELECT v.id_employee, 
             DATE_ADD(v.start, interval s.seq - 1 DAY) AS Date 
FROM vacations v CROSS JOIN seq_1_to_100 s 
WHERE DATE_ADD(v.start, interval s.seq - 1 DAY) <= v.end 
ORDER BY v.id_employee, v.start, s.seq ) t 
LEFT JOIN holidays h ON t.date=h.holiday_date
GROUP BY id_employee, EXTRACT(YEAR_MONTH FROM t.Date);

Assuming that the holidays table structure would be something like this:

CREATE TABLE holidays (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
holiday_date DATE,
holiday_description VARCHAR(255));

Then LEFT JOIN it to your current query and change the SUM() slightly by adding CASE expression to check. If the ON t.date=h.holiday_date in the left join matches, there will be result of field h.holiday_date, otherwise it will be NULL, hence only the CASE h.holiday_date WHEN IS NULL .. will be considered.

Demo fiddle

Adding this solution compatible with both MariaDB and MySQL version that supports common table expression:

WITH RECURSIVE cte AS
(SELECT id_employee, start, start lvdt, end FROM vacations 
    UNION ALL
 SELECT id_employee, start, lvdt+INTERVAL 1 DAY, end FROM cte 
   WHERE lvdt+INTERVAL 1 DAY <=end)

SELECT  id_employee,
        YEAR(v.lvdt) AS year, 
        MONTH(v.lvdt) AS month,
        SUM(CASE WHEN h.holiday_date IS NULL THEN WEEKDAY(v.lvdt) < 5 END) AS days
  FROM cte v 
  LEFT JOIN holidays h
     ON v.lvdt=h.holiday_date
  GROUP BY id_employee,
        YEAR(v.lvdt), 
        MONTH(v.lvdt);

MySQL function to find the number of working days between two dates

This expression -

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

calculates the number of business days between the start date @S and the end date @E.

Assumes end date (@E) is not before start date (@S).
Compatible with DATEDIFF in that the same start date and end date
gives zero business days.
Ignores holidays.

The string of digits is constructed as follows. Create a table of
start days and end days, the rows must start with monday (WEEKDAY
0) and the columns must start with Monday as well. Fill in the
diagonal from top left to bottom right with all 0 (i.e. there are 0
working days between Monday and Monday, Tuesday and Tuesday, etc.).
For each day start at the diagonal (must always be 0) and fill in
the columns to the right, one day at a time. If you land on a
weekend day (non business day) column, the number of business days
doesn't change, it is carried from the left. Otherwise, the number
of business days increases by one. When you reach the end of the
row loop back to the start of the same row and continue until you
reach the diagonal again. Then go on to the next row.

E.g. Assuming Saturday and Sunday are not business days -

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

Then concatenate the 49 values in the table into the string.

Please let me know if you find any bugs.

-Edit
improved table:

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

improved string: '0123444401233334012222340111123400001234000123440'

improved expression:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

Counting working days between two dates in data table using calendar table

Solution worked perfectly for me in POSTGRES:

table example
join
calendar table ON tsrange(task_assigned_date, task_got_to_work_date)&&tsrange(calendar.start_time, calendar.end_time)

---

Related Topics