计算两个日期之间的总业务工作日

如果您只想排除周末，则可以通过添加以下条件来简单地使用条件计数排除周末：

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

因此，您的查询变为：

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 weekdaysfrom departments, dbo.USERINFO INNER JOIN dbo.CHECKINOUT ON      dbo.USERINFO.USERID = dbo.CHECKINOUT.USERIDwhere  userinfo.name='Gokul Gopalakrishnan' and deptname='GEN/SUP-TBL' and checktime>='2014-05-01' and checktime<='2014-05-30'

但是，
我真的建议向您的数据库添加日历表。它使一切变得如此简单，您的查询将变为：

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.DateWHERe   d.Date >= '2014-05-01'AND     d.Date <= '2014-05-30';

这样，您可以定义公共假期，周末等。它比任何其他解决方案都更加灵活。

编辑

我想我误解了您的原始标准。这应该对您没有日历表的工作：

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 = @WorkdaysFROM    dbo.Departments AS d        CROSS JOIN dbo.userInfo AS ui        INNER JOIN dbo.CheckInOut AS cio ON cio.UserID = ui.UserIDWHERe   ui.Name = 'Gokul Gopalakrishnan' AND     d.deptname = 'GEN/SUP-TBL' AND     cio.CheckTime >= @StartDateAND     cio.CheckTime <= @EndDate;