SELECT
  SAL_DATE,
  NO,
  ITEM_CODE,
  SAL_QTY,
  DEL_QTY,
  CASE
    WHEN DEL_QTY = 0 THEN '未出荷'
    WHEN SAL_QTY > DEL_QTY THEN '一部出荷'
    ELSE '出荷済'
  END AS SHIP_STS
FROM (
  SELECT
    s.SALES_DATE AS SAL_DATE,
    s.SALES_NO AS NO,
    sd.ITEM_CODE AS ITEM_CODE,
    SUM(sd.SALES_QTY) AS SAL_QTY,
    SUM(DELIVERED_QTY) AS DEL_QTY
  FROM SALES AS s
  INNER JOIN SALES_DTL AS sd
    ON s.SALES_NO = sd.SALES_NO
  WHERE
    s.SALES_DATE BETWEEN '2024-04-01' AND '2024-04-10'
  GROUP BY
    s.SALES_DATE, s.SALES_NO, sd.ITEM_CODE
)
ORDER BY SAL_DATE DESC, NO ASC, ITEM_CODE ASC;