Calculate total business working days between two dates

Question

select  count(distinct(dateadd(d, 0, datediff(d, 0,checktime)))) as workingdays
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'

from the above code I am able to find total working days of employee between two dates.

workingdays
20

but now I want other column name total business days. I want to calculate total business days between two dates.

workingdays  businessdays
20              21

how can i do this?

what's the definition of 'business day' in your case? is it affected by national/countrywide vacation/holidays? — Paolo, Jun 25 '14 at 11:35
@Paolo...no no country related...only i want business days between two dates suppose in june there are 30 days but if we exclude saturday and sunday then there will remain only 19 days... so i want this 19 days answer — rushank shah, Jun 25 '14 at 11:36

score 4 · Accepted Answer · edited Nov 13 '18 at 23:14

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;

@GarethD...no still its not done...when i input checktime>='2014-06-01' and checktime<='2014-06-30' then it will show 12 as weekdays....it should be 21... — rushank shah, Jun 25 '14 at 11:47
i have add calendar table but still gives error calendar table not found — rushank shah, Jun 25 '14 at 11:54
That entirely depends on context, what do you mean by user side? There are so many different client side applications you could be using it would be pointless even speculating on how you would want to do this. — GarethD, Jun 25 '14 at 12:16
see i have edit ....i want to do this in my stored procedure... how can i do this??? — rushank shah, Jun 25 '14 at 12:19
I really don't understand what you are trying to do, or what you are struggling with. Could you possibly ask a new question with the full details of what you are struggling with, and how you are approaching the problem and I will try and solve the problem for you. Thanks — GarethD, Jun 25 '14 at 12:37

mehdi lotfi · Answer 2 · 2014-06-25T11:51:39.197

0

following query calculate Fridays count between @FromDate and @ToDate variable

((DATEDIFF(DAY,@FromDate,@ToDate)-(6-DATEPART(dw,@FromDate)))/7)*2

Following query calculate Working day count and business day count between to date :

DECLARE @FromDate DATE = '2014-05-01',
    @ToDate DATE = '2014-05-30'

SELECT  COUNT(DISTINCT CAST(checktime AS Date)) as workingdays,
        DATEDIFF(DAY,@FromDate,@ToDate) -
       ((DATEDIFF(DAY,@FromDate,@ToDate)-(6-DATEPART(dw,@FromDate)))/7)*2 AS BusinessDay
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>= @FromDate and checktime<=@ToDate

edited Jun 25 '14 at 11:51

answered Jun 25 '14 at 11:25

mehdi lotfi

11,194
18
82
128

nope..it gives same answer as workindays... i want to exclude weekend. – rushank shah Jun 25 '14 at 11:28
ya ...thank you..it give complete answer for 2014-05-01 to 2014-05-31 which is 22 but when i enter 2014-06-01 to 2014-06-30 then it give 23...it should be 21. – rushank shah Jun 25 '14 at 12:00

Calculate total business working days between two dates

2 Answers2

Linked