/* -----------------------------------------------------------
   QUERY: สรุปการตรวจซ้ำในช่วงเวลา (@d1 ถึง @d2)
   ใช้สำหรับ: view_lab_statistic_Order_List
   ----------------------------------------------------------- */

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

WITH Sub AS (
    SELECT 
        HN,
        [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
)

-- 1. จำนวน "คน" ที่ตรวจซ้ำ
SELECT 
    COUNT(DISTINCT CASE WHEN Days_Diff BETWEEN 1 AND 89 THEN HN END) AS [จำนวนคนที่ซ้ำ],
    COUNT(DISTINCT HN) AS [จำนวนคนทั้งหมด],
    ROUND(
        CAST(COUNT(DISTINCT CASE WHEN Days_Diff BETWEEN 1 AND 89 THEN HN END) AS FLOAT) 
        / NULLIF(COUNT(DISTINCT HN),0) * 100, 2
    ) AS [ร้อยละคนที่ซ้ำ (%)]
FROM Sub;

-- 2. จำนวน "Test" ที่ตรวจซ้ำ
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;

-- 3. จำนวน "Order (LN)" ที่ตรวจซ้ำ
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;