SELECT Main.[REQ ITEM NAME] AS [ชื่อการตรวจ],
      COUNT(DISTINCT Main.HN) AS [จำนวนคนไข้ที่ตรวจซ้ำภายใน 90 วัน]
FROM (
    SELECT HN,
           FULLNAME,
           [DORTOR NAME],
           [WARD NAME],
           LN,
           [REQ ITEM CODE],
           [REQ ITEM NAME],
           [REQ ITEM CHECKIN DATETIME] AS Current_Checkin,
           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 ไม่ต้อง fix CODE เดียว
      --AND [REQ ITEM CODE] = 'CH005'
      AND [REQ ITEM CHECKIN DATETIME] BETWEEN '2025-10-01' AND '2026-03-30'
) AS Main
WHERE Main.Days_Diff IS NOT NULL
  AND Main.Days_Diff BETWEEN 1 AND 90
GROUP BY Main.[REQ ITEM NAME]
ORDER BY [จำนวนคนไข้ที่ตรวจซ้ำภายใน 90 วัน] DESC;