select
  s.sales_date SAL_DATE
 ,s.sales_no NO
 ,dtl.ITEM_CODE ITEM_CODE
 ,dtl.sales_qty SAL_QTY
 ,dtl.delivered_qty DEL_QTY
 , 
    case
        when dtl.delivered_qty = 0 then '未出荷'
        when dtl.sales_qty  > dtl.delivered_qty then '一部出荷'
        else '出荷済'
    end AS SHIP_STS
from
 sales S
inner join
    sales_dtl dtl
on
    s.sales_no = dtl.sales_no
where
    s.sales_date between date('2024-04-01') and date('2024-04-10')
order by    
     1 desc
    ,2
    ,3