WITH DuplicateTests AS (
    SELECT 
        v.[HN],
        v.[FULLNAME] AS Name,
        v.[REQ ITEM NAME] AS TestName,
        v.[LN],
        v.[REQ ITEM ORDER DATETIME] AS OrderDate,
        COUNT(*) OVER (PARTITION BY v.[HN], v.[REQ ITEM CODE]) AS TestCount
    FROM view_lab_statistic_Order_List v
    JOIN view_lab_information_Request_Items_Specimen i
        ON i.[IREQI_LN] = v.[LN]
        AND i.IREQI_CODE = v.[REQ ITEM CODE]
    WHERE 
        (v.[order inactive] <> 'Y')
        AND (v.[req item state] <> 'R')
        AND (v.[TEST INACTIVE] <> '1')
        AND (v.[REQ ITEM ORDER DATETIME] IS NOT NULL)
        AND (v.[REQ ITEM ORDER STAFF CODE] IS NOT NULL)
        AND v.[ISP_STATE] = 'C'
        AND v.[REQ ITEM CODE] IN ('CM005','CM083','CM003','CM095','CM032','CM008','CH001P','CM006')
)
SELECT 
    HN,
    Name,
    SUM(TestCount) AS TotalDuplicateOrders,
    TestName,
    LN,
    OrderDate
FROM DuplicateTests
WHERE TestCount > 1   -- เลือกเฉพาะที่มีการสั่งซ้ำ
GROUP BY 
    HN, Name, TestName, LN, OrderDate
ORDER BY 
    Name, TestName, OrderDate;