query กายอุปกรณ์

SET @startdate = '2025-01-01';
SET @enddate   = CURDATE();
 
WITH src AS (
  SELECT
    DATE_FORMAT(o.vstdate, '%Y-%m') AS ym,
    CASE
      WHEN o.icode = '3016495' THEN 'ไม้เท้า 1ปุ่ม'
      WHEN o.icode = '3016496' THEN 'ไม้เท้า 3 ขา'
      WHEN o.icode = '3017313' THEN 'Walker'
      WHEN o.icode = '3016497' THEN 'เครื่องช่วยเดินชนิด 4 ขา (Pick-up-walker) (8701)'
      WHEN o.icode = '3016858' THEN 'เครื่องช่วยเดินชนิด 4 ขา มีล้อ (Posterior Wheel Walker) (8702)'
      WHEN o.icode = '3016862' THEN 'ที่ช่วยฝึกเดินแบบมีล้อขนาดกลาง(Anterior Wheel Walker) (8709)'
      WHEN o.icode = '3016863' THEN 'ที่ช่วยฝึกเดินแบบมีล้อขนาดเล็ก (Anterior Wheel Walker) (8710)'
      WHEN o.icode = '3016499' THEN 'รถเข็นนั่ง'
      WHEN o.icode = '3017375' THEN 'รถโยก'
      WHEN o.icode = '3017320' THEN 'ไม้ค้ำยันรักแร้'
      WHEN o.icode = '3016498' THEN 'ไม้ค้ำยันรักแร้แบบไม้ (พิการ) (8711)'
      WHEN o.icode = '3016826' THEN 'อุปกรณ์พยุงหลัง'
      WHEN o.icode IN ('3016493','3016492') THEN 'อุปกรณ์พยุงเข่า'
      WHEN o.icode = '3016491' THEN 'สายคล้องแขนกันไหล่หลุด'
      ELSE 'อื่นๆ'
    END AS item_type,
    o.qty,
    COALESCE(o.pttype, ov.pttype) AS pttype
  FROM opitemrece o
  LEFT JOIN ovst ov ON ov.vn = o.vn
  WHERE o.vstdate BETWEEN @startdate AND @enddate
    AND o.icode IN ('3016495','3016496','3017313','3016497','3016858','3016862','3016863',
                    '3016499','3017375','3017320','3016498','3016826','3016493','3016492','3016491')
),
agg AS (
  SELECT
    item_type,
    ym,
    SUM(CASE WHEN pttype = '56' THEN qty ELSE 0 END) AS s_lgo,
    SUM(CASE WHEN pttype IN ('02','03','12','60','O1') THEN qty ELSE 0 END) AS s_ofc,
    SUM(CASE WHEN pttype IN ('13','17','18','19','23','44','45','46','S4') THEN qty ELSE 0 END) AS s_sss,
    SUM(CASE WHEN pttype IN ('11','21','58','71','72','73','75','76','77','80','81','82','87','88','90','91','94','95','A5','A9','AA') THEN qty ELSE 0 END) AS s_ucs,
    SUM(CASE WHEN pttype IN ('06') THEN qty ELSE 0 END) AS s_oth,
    SUM(CASE WHEN pttype IN ('74','D1') THEN qty ELSE 0 END) AS d_ucs,
    SUM(CASE WHEN pttype IN ('99') THEN qty ELSE 0 END) AS s_pay,
    SUM(CASE WHEN pttype NOT IN ('56','02','03','12','60','O1','13','17','18','19','23','44','45','46','S4',
                                 '11','21','58','71','72','73','74','75','76','77','80','81','82','87','88',
                                 '90','91','94','95','A5','A9','AA','D1','06','99') THEN qty ELSE 0 END) AS s_other
  FROM src
  GROUP BY item_type, ym
)
SELECT
  item_type,
  ym,
  s_lgo,
  s_ofc,
  s_sss,
  s_ucs,
  s_oth,
  d_ucs,
  s_pay,
  s_other,
  (s_lgo+s_ofc+s_sss+s_ucs+s_oth+d_ucs+s_pay+s_other) AS total_qty
FROM agg
ORDER BY item_type, ym