SELECT
     v.[HN],
     v.[FULLNAME] AS PatientName,
     r.[REQ ITEM NAME] AS LabTest,
     v.[REQ ITEM ORDER DATETIME] AS OrderDate,
     COUNT(v.[LN]) AS TestCount
FROM
     view_lab_statistic_Order_List v
JOIN
     view_lab_information_Request_Items_Specimen r
     ON r.[IREQI_LN] = v.[LN]
    AND r.[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 r.[ISP_STATE] = 'C'
GROUP BY
     v.[HN],
     v.[FULLNAME],
     r.[REQ ITEM NAME],
     v.[REQ ITEM ORDER DATETIME]
ORDER BY
     v.[HN], OrderDate, LabTest;
---แต่ละรายการตรวจจะแสดง วันที่สั่งตรวจ





-------เดิมที---------------
SELECT
     v.[HN],
     v.[FULLNAME] AS PatientName,
     r.[REQ ITEM NAME] AS LabTest,
     COUNT(v.[LN]) AS TestCount
FROM
     view_lab_statistic_Order_List v
JOIN
     view_lab_information_Request_Items_Specimen r
     ON r.[IREQI_LN] = v.[LN]
    AND r.[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 r.[ISP_STATE] = 'C'
GROUP BY
     v.[HN],
     v.[FULLNAME],
     r.[REQ ITEM NAME]
ORDER BY
     v.[HN], LabTest;
------------------------------------------------------
---





---Pivot ใน SQL (ตัวอย่าง)
SELECT
    v.[HN],
    v.[FULLNAME] AS PatientName,
    SUM(CASE WHEN r.[REQ ITEM NAME] = 'CBC' THEN 1 ELSE 0 END) AS CBC,
    SUM(CASE WHEN r.[REQ ITEM NAME] = 'FBS' THEN 1 ELSE 0 END) AS FBS,
    SUM(CASE WHEN r.[REQ ITEM NAME] = 'Creatinine' THEN 1 ELSE 0 END) AS Creatinine,
    SUM(CASE WHEN r.[REQ ITEM NAME] = 'Urinalysis' THEN 1 ELSE 0 END) AS Urinalysis,
    SUM(CASE WHEN r.[REQ ITEM NAME] = 'Lipid Profile' THEN 1 ELSE 0 END) AS LipidProfile,
    SUM(CASE WHEN r.[REQ ITEM NAME] = 'HbA1c' THEN 1 ELSE 0 END) AS HbA1c
FROM
    view_lab_statistic_Order_List v
JOIN
    view_lab_information_Request_Items_Specimen r
    ON r.[IREQI_LN] = v.[LN]
   AND r.[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 r.[ISP_STATE] = 'C'
GROUP BY
    v.[HN],
    v.[FULLNAME]
ORDER BY
    v.[HN];
------------------------------

---Dynamic Pivot SQL (SQL Server)
-------------------------------------------