---นับจำนวน Test (รหัสการตรวจ) ที่ตรวจซ้ำ และคิดเป็นร้อยละของ Test ทั้งหมด
DECLARE @d1 DATE = '2025-10-01';
DECLARE @d2 DATE = '2026-03-30';

WITH Sub AS (
    SELECT 
        [REQ ITEM CODE] AS TestCode,
        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 
    COUNT(DISTINCT CASE WHEN Days_Diff BETWEEN 1 AND 89 THEN TestCode END) AS [จำนวนTestที่ซ้ำ],
    COUNT(DISTINCT TestCode) AS [จำนวนTestทั้งหมด],
    ROUND(
        CAST(COUNT(DISTINCT CASE WHEN Days_Diff BETWEEN 1 AND 89 THEN TestCode END) AS FLOAT) 
        / NULLIF(COUNT(DISTINCT TestCode),0) * 100, 2
    ) AS [ร้อยละTestที่ซ้ำ (%)]
FROM Sub;