DELETE
FROM ITEM

WHERE 
 STOCK_MANAGEMENT_TYPE = 1
 AND(
  NOT EXISTS (
   SELECT
    ITEM_CODE
   FROM
    STOCK
   WHERE
    STOCK.ITEM_CODE = ITEM.ITEM_CODE
   )
 
  OR EXISTS (
   SELECT
    ITEM_CODE
   FROM
    STOCK
   WHERE
    STOCK.ITEM_CODE = ITEM.ITEM_CODE
   GROUP BY 
    ITEM_CODE
   HAVING
    MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-06-01', '-6 months')
    
   AND SUM(ACTUAL_AMT) = 0
  
  
  ) 
 
 );