选择日期范围内的特定日期

I have the query below which gives me the number of days that a student was absent. DATEDIFF and DATEPART calculate the weekdays and holidays should not be counted as an absent day. The absent days stored in studentTable in two fields which are fromDate and toDate. So the absent days are in a date range. If a student is one day absent, it is recorded 11/23/2015, 11/23/2015. If a student is two days absent ,then 11/23/2015, 11/24/2015.

我在下面的查询中给出了学生缺席的天数。 DATEDIFF和DATEPART计算工作日和假日不应计入缺席日。缺少的日子存储在studentTable的两个字段中,分别来自日期和日期。所以缺席的日子是在日期范围内。如果学生缺席一天,则会记录在2015年11月23日,2015年11月23日。如果学生缺席两天,那么2015年11月23日,2015年11月24日。

DECLARE @startDate DATE SET @startDate = '20151121'
DECLARE @endDate DATE SET @endDate = '20151123'       

SELECT      
  a.studentName

  ,SUM(DATEDIFF(dd, fromDate, toDate) 
  - (DATEDIFF(wk, fromDate, toDate) * 2) 
  -CASE WHEN DATEPART(dw, fromDate) = 1 THEN 1 ELSE 0 END 
  +CASE WHEN DATEPART(dw, toDate) = 1 THEN 1 ELSE 0 END + 1 )- COUNT(h.holiday)
   AS totalAbsentDay        

  FROM studentTable a 
LEFT OUTER JOIN holiday h 
ON h.holiday < a.toDate and h.holiday > a.fromDate

WHERE a.fromDate = @startDate AND a.toDate = @endDate
GROUP BY a.studentName

The problem here is that when I try to declare a start and an end date, it does not give me the correct absent days. For example, if a student is absent between 11/23/2015 and 11/26/2015 which is 4 days absent , and I declare start date 11/22/2015 and end date 11/27/2015, the query doesn’t give me the result of 3.

这里的问题是,当我尝试声明一个开始和结束日期时,它并没有给我正确的缺席日期。例如,如果学生在2015年11月23日至2015年11月26日期间缺席,且缺席4天,并且我宣布2015年11月22日开始日期和2015年11月27日结束日期,则查询不会给我结果3。

1 个解决方案

#1

This query below will work for given database scheme, may not the best solution because use three level of queries

下面的查询将适用于给定的数据库方案,可能不是最佳解决方案,因为使用三级查询

DECLARE @startDate DATE SET @startDate = '2016-02-05'
DECLARE @endDate DATE SET @endDate = '2016-02-20'       

SELECT
    studentName,
    SUM(AbsentDay) totalAbsentDay
FROM
(
    SELECT      
      a.studentName
      ,DATEDIFF(dd, fromDate, toDate) 
      - (DATEDIFF(wk, fromDate, toDate) * 2) 
      -CASE WHEN DATEPART(dw, fromDate) = 1 THEN 1 ELSE 0 END 
      +CASE WHEN DATEPART(dw, toDate) = 1 THEN 1 ELSE 0 END + 1 - COUNT(h.holiday)
       AS AbsentDay        
      FROM (
            SELECT
                studentName,-- Name,
                CASE WHEN fromDate<@startDate THEN @startDate ELSE fromDate END fromDate,
                CASE WHEN toDate>@endDate THEN @endDate ELSE toDate END toDate
            FROM 
                StudentTable S
            WHERE 
                S.toDate >= @startDate AND s.fromDate <= @endDate
        ) a 
        LEFT OUTER JOIN holiday h 
        ON h.holiday < a.toDate and h.holiday > a.fromDate

    GROUP BY studentName, fromDate, toDate
) B
GROUP BY studentName

For easier query and faster execution please consider to redesign studentTable to something like idStudent, AbsentDate..just a suggestion..

为了更容易查询和更快的执行,请考虑重新设计studentTable,如idStudent,AbsentDate ..一个建议..

#1