在SQL表中查找日期差距(缺少日期范围

Question

在这个SQL Server 2008数据库中，我有一个考勤表，学生们每天都会来学校报到，这个表看起来像这样：

SchoolID | StudentID | Date 

这张桌子上的每个学生每天都会有一个记录。我想要找出的是，给定开始日期、结束日期和天数(间隔)，找出任何在这些天数内没有报到学校的学生。例如，我需要知道哪些学生在12月份连续错过了3天，并列出了StudentID的列表。

我怎么才能完成这样的事情呢？

Answer 1

您可以生成从startdate到enddate的日期范围，然后将此数据与您的表进行外部连接，如果学生不会将其视为1，则汇总此数据。

要生成日期范围，可以使用此函数，如下所示

CREATE FUNCTION [dbo].[DateRange]
(     
      @Increment              CHAR(1),
      @StartDate              DATETIME,
      @EndDate                DATETIME
)
RETURNS  
@SelectedRange    TABLE 
(IndividualDate DATETIME)
AS 
BEGIN
      ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                  END)

      INSERT INTO @SelectedRange (IndividualDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END
GO 

然后

    select sum(isAbsent) absentDays, s.studentid from
    (
    select case when studentid is null then 1 else 0 end isAbsent,individualDate,s.studentid from DateRange('d', '01/11/2014', '30/11/2014') d
cross join tblstudent s
    left outer join yourtable on yourtable.Date = d.IndividualDate and yourtable.studentid = s.studentid 
    ) x
    group by s.studentid
    having sum(isAbsent) > 3

Answer 2

看看这个。我想你将能够从that.This解决方案中找到你自己的答案，照顾周末和节假日：

SQL Fiddle

MS SQL Server2008架构安装程序

CREATE TABLE attendance
    ([SchoolID] int, [StudentID] int, [Date] datetime)
;

INSERT INTO attendance
    ([SchoolID], [StudentID], [Date])
VALUES
    (1, 1, '2014-12-01 00:00:00'),
    (1, 1, '2014-12-02 00:00:00'),
    (1, 1, '2014-12-03 00:00:00'),
    (1, 1, '2014-12-04 00:00:00'),
    (1, 1, '2014-12-05 00:00:00'),
    (1, 1, '2014-12-08 00:00:00'),
    (1, 1, '2014-12-09 00:00:00'),
    (1, 1, '2014-12-10 00:00:00'),
    (1, 1, '2014-12-11 00:00:00'),
    (1, 1, '2014-12-12 00:00:00'),
    (1, 1, '2014-12-15 00:00:00'),
    (1, 1, '2014-12-16 00:00:00'),
    (1, 1, '2014-12-17 00:00:00'),
    (1, 1, '2014-12-18 00:00:00'),
    (1, 1, '2014-12-19 00:00:00'),
    (1, 2, '2014-12-01 00:00:00'),
    (1, 2, '2014-12-02 00:00:00'),
    (1, 2, '2014-12-08 00:00:00'),
    (1, 2, '2014-12-09 00:00:00'),
    (1, 2, '2014-12-10 00:00:00'),
    (1, 2, '2014-12-11 00:00:00'),
    (1, 2, '2014-12-12 00:00:00'),
    (1, 2, '2014-12-15 00:00:00'),
    (1, 2, '2014-12-16 00:00:00'),
    (1, 2, '2014-12-17 00:00:00'),
    (1, 2, '2014-12-18 00:00:00'),
    (1, 2, '2014-12-19 00:00:00')
;

CREATE TABLE holidays
    ([Date] datetime)
;

INSERT INTO holidays
    ([Date])
VALUES
    ('2014-12-22 00:00:00'),
    ('2014-12-23 00:00:00'),
    ('2014-12-24 00:00:00'),
    ('2014-12-25 00:00:00'),
    ('2014-12-26 00:00:00'),
    ('2014-12-29 00:00:00'),
    ('2014-12-30 00:00:00'),
    ('2014-12-31 00:00:00')
;


CREATE TABLE students
    ([StudentID] int, [Name] varchar(5))
;

INSERT INTO students
    ([StudentID], [Name])
VALUES
    (1, 'John'),
    (2, 'Peter')
;

查询1

DECLARE @start DATE, @end DATE
SELECT @start = '20141201', @end = '20141231'

;WITH tdate AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
)

SELECT DISTINCT Name
FROM students s 
INNER JOIN attendance a ON s.StudentID = a.StudentID
INNER JOIN tdate ON DATEADD(DAY, n-1, @start) = a.Date
GROUP BY NAME
HAVING 
(SELECT count(*)
FROM tdate
LEFT OUTER JOIN holidays h ON DATEADD(DAY, n-1, @start) = h.Date
WHERE h.date is null
AND DATEPART(dw,DATEADD(DAY, n-1, @start)) not in (1,7))
- COUNT(*) >= 3

|  NAME |
|-------|
| Peter |

更新

SELECT s.StudentID, d.Date
FROM students s
INNER JOIN (
SELECT DATEADD(DAY, n-1, @start) as Date
FROM tdate
LEFT OUTER JOIN holidays h ON DATEADD(DAY, n-1, @start) = h.Date
WHERE h.date is null
AND DATEPART(dw,DATEADD(DAY, n-1, @start)) not in (1,7)) d ON 1 = 1
LEFT OUTER JOIN attendance a ON s.StudentID = a.StudentID AND d.Date = a.Date
WHERE a.StudentID IS NULL
ORDER BY s.StudentID, d.Date

| STUDENTID |       DATE |
|-----------|------------|
|         2 | 2014-12-03 |
|         2 | 2014-12-04 |
|         2 | 2014-12-05 |