WITH KPI_Target AS (
    SELECT 'CH005' AS TestCode, 5 AS TargetPercent
    UNION ALL SELECT 'CH060', 15
		UNION ALL SELECT 'CH059', 15
		UNION ALL SELECT 'CH058', 15
    UNION ALL SELECT 'HM0001', 5
),
Sub AS (
    SELECT 
        [REQ ITEM CODE] AS TestCode,
        [REQ ITEM NAME] AS TestName,
        LN,
        DATEDIFF(day, 
            LAG([REQ ITEM CHECKIN DATETIME]) OVER (
                PARTITION BY HN, [REQ ITEM CODE] 
                ORDER BY [REQ ITEM CHECKIN DATETIME]
            ), 
            [REQ ITEM CHECKIN DATETIME]
        ) AS Days_Diff
    FROM view_lab_statistic_Order_List
    WHERE [REQ ITEM CHECKIN DATETIME] BETWEEN '@d1' AND '@d2'
)
SELECT 
    S.TestCode,
    S.TestName,
    COUNT(S.LN) AS [จำนวนตรวจทั้งหมด],
    SUM(CASE WHEN S.Days_Diff BETWEEN 1 AND 89 THEN 1 ELSE 0 END) AS [จำนวนที่ซ้ำ],
    ROUND(
        CAST(SUM(CASE WHEN S.Days_Diff BETWEEN 1 AND 89 THEN 1 ELSE 0 END) AS FLOAT) 
        / NULLIF(COUNT(S.LN),0) * 100, 2
    ) AS [ร้อยละการตรวจซ้ำ],
    ISNULL(K.TargetPercent, 5) AS [KPI เป้าหมาย],
    CASE 
        WHEN (CAST(SUM(CASE WHEN S.Days_Diff BETWEEN 1 AND 89 THEN 1 ELSE 0 END) AS FLOAT) 
              / NULLIF(COUNT(S.LN),0) * 100) < K.TargetPercent 
        THEN 'ผ่าน' ELSE 'เกินเป้า'
    END AS [สถานะ KPI]
FROM Sub S
LEFT JOIN KPI_Target K ON S.TestCode = K.TestCode
GROUP BY S.TestCode, S.TestName, K.TargetPercent
ORDER BY [ร้อยละการตรวจซ้ำ] DESC;



--------------------------------------แสดงความแตกต่างของรายการตรวจครั้งล่าสุดกับครั้งก่อนหน้า
SELECT 
    Main.HN,
    Main.FULLNAME AS [ชื่อ-นามสกุล],
    Main.[REQ ITEM CODE] AS [รหัสการตรวจ],
    Main.[REQ ITEM NAME] AS [ชื่อการตรวจ],
    Main.Current_Checkin AS [วันที่ตรวจครั้งนี้],
    Main.Last_Checkin AS [วันที่ตรวจครั้งก่อนหน้า],
    Main.Days_Diff AS [ระยะห่าง (วัน)],
    Main.[DORTOR NAME] AS [แพทย์ผู้สั่งตรวจ],
    Main.[WARD NAME] AS [หน่วยงาน/วอร์ด],
    Main.LN AS [Lab Number (ครั้งนี้)]
FROM (
    /* ส่วนดึงข้อมูลและหาประวัติการตรวจครั้งก่อนหน้าโดยใช้ Window Function LAG() */
    SELECT 
        HN,
        FULLNAME,
        [DORTOR NAME],
        [WARD NAME],
        LN,
        [REQ ITEM CODE],
        [REQ ITEM NAME],
        [REQ ITEM CHECKIN DATETIME] AS Current_Checkin,
        
        -- หาเวลา Checkin ของการตรวจครั้งก่อนหน้า โดยแบ่งกลุ่มตาม HN และ รหัสการตรวจ
        LAG([REQ ITEM CHECKIN DATETIME]) OVER (
            PARTITION BY HN, [REQ ITEM CODE] 
            ORDER BY [REQ ITEM CHECKIN DATETIME]
        ) AS Last_Checkin,
        
        -- คำนวณความต่างของวันระหว่างครั้งปัจจุบันกับครั้งก่อนหน้า
        DATEDIFF(day, 
            LAG([REQ ITEM CHECKIN DATETIME]) OVER (
                PARTITION BY HN, [REQ ITEM CODE] 
                ORDER BY [REQ ITEM CHECKIN DATETIME]
            ), 
            [REQ ITEM CHECKIN DATETIME]
        ) AS Days_Diff
    FROM view_lab_statistic_Order_List
    WHERE 1=1
      -- ตัวอย่างการกรองชื่อ Test (พี่จีจีเปลี่ยนตรงนี้ได้เลยครับ)
      ---AND [REQ ITEM CODE] = 'HM0001' 
      
      -- กำหนดช่วงวันที่ต้องการดึงรายงาน (เปลี่ยนช่วงวันที่ได้ตามต้องการ)
      AND [REQ ITEM CHECKIN DATETIME] BETWEEN '2025-10-01 00:00' AND '2026-04-17 23:59'
) AS Main
WHERE Main.Days_Diff IS NOT NULL 
  AND Main.Days_Diff BETWEEN 1 AND 89 -- เงื่อนไข KPI: มีการตรวจซ้ำภายใน 90 วัน
ORDER BY Main.Days_Diff ASC, Main.HN;