DECLARE @d1 DATE = '2025-10-01';
DECLARE @d2 DATE = '2026-03-30';

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