如果您只想排除周末，则可以通过添加以下条件来简单地使用条件计数排除周末：
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;
