從數據庫出勤中獲得出席、缺席和沒有上課日

問題

問題是我能夠得到教師記錄出勤的天數，只有這樣，我無法得到他們沒有出勤的天數和教師不在場的天數。

這是表的部分模式

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
------ | ----------------- | -------------- | ---------- | ------------ | -------- | ----------- | ---------- | ---------- | ---------- | ---------- | ---------------

我們跟踪出席情況的附表

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

結果使用PIVOT並看起來像這樣

建議的解決方案，我需要您的建議

現在感覺我們沒有上課日的記錄（而且它們沒有標準化）我的建議是我們做這樣的事情

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

並使用DATENAME. 對於一個簡單的查詢，這不會有任何問題，但是使用動態數據透視查詢我最終會得到與上面相同的結果。有什麼建議麼？

**編輯：**這是查詢

-- 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

因為我應該發布解決方案而不是“nvm我想通了”，所以這是解決方案，以防有一天有人需要它

解決方案

我創建了一個section_days表，其中天數為天數

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

以下查詢獲取所有上課日並將它們分配為缺席N然後另一個查詢是原始查詢將N更新為Y為基於sec_id

現在執行查詢將生成三個值（Y，N和NULL），它們對應於我們首先需要的

• Y = 現在
• N = 缺席
• NULL = 無類

這是對查詢的修改供您參考

   -- ------------------------------------------------------------------------------
-- ↓ 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

引用自：https://dba.stackexchange.com/questions/225089