时间戳

首页 > 解决方案 > 显示学生缺勤日期

sql - 显示学生缺勤日期

问题描述

这是我要显示的表格：

   tblAttendance table
    CustomerId
    Id
    Attendence 
    Date
    and
    tblStudent 
    CustomerId
    Name

现在我想从到日期搜索并想要缺席日期。我怎样才能做到这一点？

我尝试了以下代码：

ALTER PROCEDURE spExceptDate

AS
declare @StartDate DATE, @EndDate DATE
set @StartDate = '2020-02-15';
set @EndDate = '2020-02-25';
BEGIN


 SELECT CustomerId,FirstName+' '+LastName,Date,Attendance 
FROM
[dbo].[tblAttendance] att
LEFT JOIN
[dbo].[tblStudent] st
ON att.CustomerId = st.Code

EXCEPT

 SELECT CustomerId,FirstName+' '+LastName,Date,Attendance 
FROM
[dbo].[tblAttendance] att
LEFT JOIN
[dbo].[tblStudent] st
ON att.CustomerId = st.Code
where att.Date>='2020-02-15' and att.Date<='2020-02-25'

END
GO

我想要学生缺席的日期

标签： sqlsql-serverstored-procedures

解决方案

基本上你需要的是From和之间的可能日期列表To

DECLARE @StartDate DATE = '2020-02-15',
        @EndDate DATE = '2020-02-25' ;

--Create a CTE to get all dates between from and to (you should filter holidays and weekends)
WITH SchoolCalendar (WorkingDay)
AS (SELECT @StartDate
    UNION ALL
    SELECT DATEADD(DAY, 1, WorkingDay)
    FROM SchoolCalendar 
    WHERE WorkingDay< @EndDate
   )

--Use the CTE to determine the Absense records
SELECT st.Code CustomerId, st.FirstName+' '+st.LastName Name,st.WorkingDay Date, COALESCE(Attendance,'A')  Attendance
FROM (SELECT * from SchoolCalendar, tblStudent) st
    LEFT JOIN [dbo].[tblAttendance] att ON att.Date = st.WorkingDay AND att.CustomerId = st.Code
WHERE st.WorkingDay>=@StartDate and st.WorkingDay<=@EndDate
ORDER BY st.Code, st.WorkingDay

推荐阅读