-- SELECT
--     SA.SALES_DATE AS SAL_DATE
--     , SA.SALES_NO AS NO
--     , SAD.ITEM_CODE AS ITEM_CODE
--     , SAD.SALES_QTY AS SAL_QTY
--     , SAD.DELIVERED_QTY AS DEL_QTY
--      -- CASE句で売上数量、出荷数量の値で出荷状況をセットする
--     , CASE 
--         WHEN SAD.DELIVERED_QTY = 0 
--             THEN '未出荷' 
--         WHEN SAD.SALES_QTY > SAD.DELIVERED_QTY 
--             THEN '一部出荷' 
--         WHEN SAD.SALES_QTY = SAD.DELIVERED_QTY 
--             THEN '出荷済' 
--         ELSE NULL 
--         END AS SHIP_STS 
-- FROM
--     SALES AS SA 
--     -- 売上データ明細を売上番頭で内部結合する
--     INNER JOIN SALES_DTL AS SAD 
--         ON SAD.SALES_NO = SA.SALES_NO 
-- WHERE
--   -- BETWEEN句で売上日を範囲指定する
--     SA.SALES_DATE BETWEEN '2024-04-01' AND '2024-04-10' 
-- ORDER BY
--   -- 売上日は降順指定なので気を付ける
--     SA.SALES_DATE DESC
--     , SA.SALES_NO ASC
--     , SAD.ITEM_CODE ASC;

select
    sa.sales_date as SAL_DATE,
    sa.sales_no as NO,
    sd.item_code as ITEM_CODE,
    sd.sales_qty as SAL_QTY,
    sd.delivered_qty as DEL_QTY,
    case 
        when sd.sales_qty = 0 THEN '未出荷'
        when sd.sales_qty > sd.delivered_qty THEN '一部出荷'
        when sd.sales_qty = sd.delivered_qty THEN '出荷済'
        ELSE NULL
    end as SHIP_STS
from
    sales as sa
    inner join SALES_DTL as sd
    on sa.sales_no = sd.sales_no
where
    sa.sales_date BETWEEN '2024-04-01' AND '2024-04-10'
    -- sa.sales_date >= '2024-04-01' and sa.sales_date <= '2024-04-10' 
ORDER BY
    sa.sales_date DESC,
    sa.sales_no asc,
    sd.item_code asc