1

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 result of the query

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

  • Welcome to Stack Exchange. The query you have so far is missing from the question. Can you add it? The formula is basically: (Absent Days) + (Present Days) = (Total days of class) ... You'll need two pieces of info to get the third. How can we determine class days? Would it make sense to assume that "total days" would be "any day with at least one student in attendance"? – AMtwo Dec 16 '18 at 12:32
  • thanks for pointing out that I forgot to add the query. I have added the query in the question. TLDR: it is safe to assume that "total days" would be "any day with at least one student in attendance" Regarding the formula, it is that simple if we were recording absence in the first place, the whole system relies on the teacher recording attendance and I think those who have made it didn't take into account the teacher being absent himself and that is my task to fix. So that simple table should solve that issue if possible. – Hoshani Dec 16 '18 at 14:39
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.