WITH tmp_table as (

Select *
From SALES
Left join SALES_DTL
ON  SALES.SALES_NO = SALES_DTL.SALES_NO
WHERE SALES_DATE between '2024-04-01' and
'2024-04-10'
)


Select 
	SALES_DATE as SAL_DATE,
	SALES_NO as NO,
	ITEM_CODE as ITEM_CODE,
	SALES_QTY as SAL_QTY,
	DELIVERED_QTY as DEL_QTY,
	CASE
		WHEN DELIVERED_QTY = 0 THEN "未出荷"
		WHEN SALES_QTY > DELIVERED_QTY THEN "一部出荷"
		WHEN SALES_QTY = DELIVERED_QTY THEN "出荷済" END
	as SHIP_STS
From tmp_table
Order by SAL_DATE desc,NO asc,ITEM_CODE asc;