-- DELETE FROM ITEM
-- WHERE ITEM_CODE IN (
-- SELECT i.ITEM_CODE
-- ,LAST_DELIVERY_DATE
-- ACTUAL_AMT
-- FROM ITEM as i
--   INNER JOIN STOCK as s
--   on i.ITEM_CODE = s.ITEM_CODE
-- where 
--  ( LAST_DELIVERY_DATE <= '2022-12-01' and ACTUAL_AMT = 0)
-- group by ITEM_CODE
-- ) or
-- ITEM_CODE NOT IN (
--     SELECT i.ITEM_CODE
-- FROM ITEM as i
--   INNER JOIN STOCK as s
--   on i.ITEM_CODE = s.ITEM_CODE
-- )
-- and STOCK_MANAGEMENT_TYPE = '1';


DELETE FROM ITEM
WHERE ITEM_CODE IN (
SELECT i.ITEM_CODE
FROM ITEM as i
  INNER JOIN STOCK as s
  on i.ITEM_CODE = s.ITEM_CODE
where 
  LAST_DELIVERY_DATE <= '2022-12-01'
group by i.ITEM_CODE
having ACTUAL_AMT = 0
)
or
ITEM_CODE NOT IN (
    SELECT i.ITEM_CODE
FROM ITEM as i
  INNER JOIN STOCK as s
  on i.ITEM_CODE = s.ITEM_CODE
)
and STOCK_MANAGEMENT_TYPE = '1'
;
-- SELECT * FROM ITEM;