Getting attended, absent and no class days from database attendance

Question

Problem

the problem is that I am able to get the days instructors record attendance and only that, I am unable to get the days they didn't make attendance and the days the instructors were not present.

here is the table's schema for sections

Sec_ID | Education_Type_ID | Time_Period_ID | Teacher_ID | Classroom_ID | Comments | Total_Seats | Created_BY | Created_ON | Updated_By | Updated_ON | Update_Comments
------ | ----------------- | -------------- | ---------- | ------------ | -------- | ----------- | ---------- | ---------- | ---------- | ---------- | ---------------

the accompanying table where we keep track of the attendance

Sec_ID | Teacher_ID | Recorded_On
------ | ---------- | -----------

result uses PIVOT and looks like this

proposed solution where I need your advice

now sense we have no record of class days (and they are not standardized) my suggestion was that we do something like this

Sec_ID | Day_of_Week
------ | -----------

and use DATENAME . For a simple query this would work with no problems, however using dynamic pivot query I would end up with the same result above. any suggestions?

EDIT: here is the query

-- pre = checking variables

-- ------------------------------------------------------------------------------
-- ↓ getting attendance days for teachers from attendance table ↓
-- ------------------------------------------------------------------------------

;WITH cte2
AS (SELECT DISTINCT
    ATT_Date,
    Sec_ID,
    Teacher_ID
FROM Attendance_STD
WHERE ATT_Date BETWEEN CONVERT(date, @p_MinDate) AND CONVERT(date, @p_MaxDate))

SELECT
    ATT_Date,
    CONVERT(NVARCHAR, [ATT_Date], 126) 
            + ' | '
            + SUBSTRING ( FORMAT ( [ATT_Date], 'dddd') ,0 , 4 )  
            AS ATT_Date,
    Sec_ID,
    r.Full_Name,
    t.Teacher_ID,
    r.User_Id,
    'Y' AS att 
INTO #TEMPATTDAYS
FROM cte2
INNER JOIN Teacher t
    ON cte2.Teacher_ID = t.Teacher_ID
INNER JOIN Regt_user r
    ON t.User_ID = r.User_Id
GROUP BY ATT_Date,
         Sec_ID,
         r.Full_Name,
         t.Teacher_ID,
         r.User_Id

-- ------------------------------------------------------------------------------
-- ↓ getting all days in the period requested ↓
-- ------------------------------------------------------------------------------

INSERT INTO #TEMPALLDAYS
SELECT 
        CAST(a.DATE AS varchar)
        + ' | '
        + SUBSTRING ( FORMAT ( a.DATE, 'dddd') ,0 , 4 )  
        AS ATT_Date
    FROM (
        SELECT TOP (DATEDIFF(
        DAY, CONVERT(date, GETDATE()-10), CONVERT(date, GETDATE())
        ) + 1)
            Date = DATEADD(
            DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1,
            CONVERT(date, GETDATE()-10))

        FROM sys.all_objects a
        CROSS JOIN sys.all_objects b) a

-- ------------------------------------------------------------------------------
-- ↓ generating columns for dynamic pivot ↓
-- ------------------------------------------------------------------------------

DECLARE @cols nvarchar(max);

SELECT
    @cols = COALESCE(@cols + ',['
    + CONVERT(nvarchar, ATT_Date, 126)
    + ']'
    , '['
    + CONVERT(nvarchar, ATT_Date, 126)
    + ']')
FROM (SELECT DISTINCT
    ATT_Date
FROM #TEMPALLDAYS) PV
ORDER BY ATT_Date

-- ------------------------------------------------------------------------------
-- ↓ final query ↓
-- ------------------------------------------------------------------------------

DECLARE @query nvarchar(max)

SET @query = '
    SELECT
        *
    FROM (SELECT
        a.ATT_Date,
        a.Teacher_ID,
        Full_Name,
        a.att,
        a.Sec_ID
    FROM #TEMPATTDAYS a
    INNER JOIN Section HS
        ON A.Sec_ID = HS.Sec_ID
        AND HS.Status_KeyNUM_49 = 1
    INNER JOIN Location HL
        ON HS.Location_ID = HL.Location_ID
    INNER JOIN SET_Period P
        ON HS.Period_ID = P.Period_ID
    INNER JOIN SET_Education_Type ET
        ON HS.Education_Type_ID = ET.Education_Type_ID
    GROUP BY a.ATT_Date,
             a.Teacher_ID,
             Full_Name,
             a.att,
             a.Sec_ID,
             HL.Area,
             P.Period_Desc,
             ET.Edu_Type_Desc,
             HL.Name) AS s
    PIVOT
    (
    MAX(att)
    FOR
    [ATT_Date] 
    IN (' + @cols + ')  
    ) AS pvt  
    ORDER BY [Location_Name]
'

-- post = dropping temp tables

Answer 1

since I should post the solution instead of "nvm I figured it out", here is the solution in case someone someday ever need it

Solution

I have created a section_days table with days being day number

Sec_ID | Day_of_Week
------ | -----------

And the following query gets all class days and assign them as absent N then another query which was the original query updates N to Y for based on sec_id

now running the query will generate three values (Y,N and NULL) which corresponds to what we needed in the first place where

• Y = present
• N = absent
• NULL = no class

here is the modification to the query for your reference

    -- ------------------------------------------------------------------------------
-- ↓ getting active days for each section and assigning them as Absent => 'N' ↓
-- ------------------------------------------------------------------------------

;WITH CTE(dateOfActiveDays)
AS
    (
    SELECT @p_MinDate
    UNION ALL
    SELECT DATEADD(d, 1, dateOfActiveDays) FROM CTE
    WHERE dateOfActiveDays < @p_MaxDate
    )

SELECT 
    HSD.Sec_ID,
    CTE.dateOfActiveDays
            + SUBSTRING ( FORMAT ( CTE.dateOfActiveDays, 'dddd') ,0 , 4 )  
            AS ATTDate,
    FORMAT(dateOfActiveDays, 'yyyy-MM-dd' ) AS ATT_DATE,
    CTE.dateOfActiveDays, 
    R.Full_Name,
    T.Teacher_ID,
    R.User_Id,
    'N' AS att 
INTO #TEMPATTDAYS 
FROM CTE  
INNER JOIN Section_Days HSD 
    ON DATEPART(WEEKDAY, dateOfActiveDays) = HSD.DayNumber
INNER JOIN Section HS 
    ON HS.Sec_ID = HSD.Sec_ID
INNER JOIN Teacher T
    ON T.Teacher_ID = HS.Teacher_ID
INNER JOIN Regt_user R
    ON T.User_ID = R.User_Id
GROUP BY CTE.dateOfActiveDays,
         HSD.Sec_ID,
         r.Full_Name_Official_AR,
         t.Teacher_ID,
         r.User_Id

-- ------------------------------------------------------------------------------
-- ↓ getting attendance days for teachers from attendance table ↓
-- ------------------------------------------------------------------------------

;WITH CTE2
AS (
    SELECT DISTINCT
    att_Date,
    Sec_ID,
    Teacher_ID
FROM Attendance_STD
WHERE att_Date BETWEEN CONVERT(DATE, @p_MinDate) AND CONVERT(DATE, @p_MaxDate)
    )

UPDATE T SET T.att = 'Y'
FROM #TEMPATTDAYS T
INNER JOIN CTE2
    ON CTE2.att_Date = T.dateOfActiveDays
    AND CTE2.Sec_ID = T.Sec_ID