query หัตถการแผนไทย

SET @startdate = '2025-01-01';
SET @enddate   = CURDATE();
 
SELECT
  o.hn,
  GROUP_CONCAT(DISTINCT o.vn ORDER BY o.vn SEPARATOR ' / ')          AS vn_list,
  GROUP_CONCAT(DISTINCT o.vstdate ORDER BY o.vstdate SEPARATOR ', ') AS visit_date_list,
  CONCAT(p.pname,' ',p.fname,' ',p.lname)                            AS pt_name,
 
  -- Dx หลักของ VN นั้น ๆ
  GROUP_CONCAT(DISTINCT d.icd10 ORDER BY d.icd10 SEPARATOR ', ')     AS diagnosis_list,
 
  -- รายการหัตถการแพทย์แผนไทยจาก module
  GROUP_CONCAT(DISTINCT hmoi.icd10tm ORDER BY hmoi.icd10tm SEPARATOR ', ')                             AS procedure_code_list,
  GROUP_CONCAT(DISTINCT hmoi.health_med_operation_item_name ORDER BY hmoi.health_med_operation_item_name SEPARATOR ', ') AS procedure_name_list
 
FROM health_med_service hms
JOIN ovst o                         ON o.vn = hms.vn
JOIN patient p                      ON p.hn = o.hn
JOIN ovstdiag d                     ON d.vn = o.vn AND d.diagtype = '1'
JOIN health_med_service_operation hmso
     ON hmso.health_med_service_id = hms.health_med_service_id
JOIN health_med_operation_item hmoi
     ON hmoi.health_med_operation_item_id = hmso.health_med_operation_item_id
 
WHERE o.vstdate BETWEEN @startdate AND @enddate
  AND d.icd10 = 'Z392'
  AND hmoi.icd10tm IN ('9007712','9007713','9007714','9007716','9007730')
 
GROUP BY o.hn, pt_name
ORDER BY MAX(o.vstdate) DESC;