with st as( select
*
from sales where sales_date between '2024-04-01' and '2024-04-10'
)
select 
st.sales_date as SAL_DATE
, st.sales_no as NO
, sales_dtl.ITEM_CODE
, sales_dtl.SALES_QTY as SAL_QTY
, sales_dtl.DELIVERED_QTY as DEL_QTY
, case
when sales_dtl.DELIVERED_QTY = 0 then '未出荷'
when sales_dtl.SALES_QTY > sales_dtl.DELIVERED_QTY then '一部出荷'
else '出荷済'
end as SHIP_STS
from st join sales_dtl on st.sales_no = sales_dtl.sales_no
order by SAL_DATE desc, NO asc, ITEM_CODE asc