SELECT tripId,
  CASE
    WHEN total_task = delivered_task THEN 'COMPLETE_DELIVERY'
    WHEN total_task = delivered_task + returned_task THEN 'DELIVERED_RETURNED'
    WHEN delivered_task = '0' THEN 'NO_DELIVERY'
    ELSE 'PARTIAL_DELIVERY'
  END AS delivery_Type
FROM (
    SELECT DISTINCT tripId,
      COUNT(DISTINCT taskId) AS total_task,
      SUM(deliveredOrder) AS delivered_task,
      SUM(returnedOrder) AS returned_task
    FROM (
        SELECT tripId,
          CASE
            WHEN deliveryStatus IN ('DELIVERED') THEN 1
            ELSE 0
          END AS deliveredOrder,
          CASE
            WHEN deliveryStatus IN ('RETURNED') THEN 1
            ELSE 0
          END AS returnedOrder,
          taskId
        FROM lmd_task_db_snapshot task
        WHERE tripId IN (
           
          ) AND scheduleStart >= '2025-10-08 13:00:00.0'
          AND scheduleStart < '2025-10-10 08:00:00.0'
      ) I
    GROUP BY tripId
  )