SELECT
SAL_DATE
,NO
,ITEM_CODE
,SAL_QTY
,DEL_QTY
,CASE
  WHEN DEL_QTY = 0 THEN '未出荷'
  WHEN DEL_QTY < SAL_QTY THEN '一部出荷'
  WHEN DEL_QTY = SAL_QTY THEN '出荷済'
  END as SHIP_STS
FROM
(SELECT
A.SALES_DATE as SAL_DATE
,A.SALES_NO as NO
,B.ITEM_CODE as ITEM_CODE
,sum(B.SALES_QTY) as SAL_QTY
,sum(B.DELIVERED_QTY) as DEL_QTY
FROM SALES as A
LEFT JOIN SALES_DTL AS B
ON A.SALES_NO = B.SALES_NO
GROUP BY A.SALES_DATE,A.SALES_NO,B.ITEM_CODE)
WHERE SAL_DATE between '2024-04-01' and '2024-04-10'
ORDER BY SAL_DATE desc,NO asc,ITEM_CODE asc