How to use results of subquery in a parent query case statement

You can use a subquery or CTE:

SELECT lh.*, . . .   -- any expressions you want
       (case when last_delivery_id = 1 then 'pending'
             when last_delivery_id = 2 then 'active'
        end) as status
FROM (SELECT l.*,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.h.created_at DESC
              LIMIT 1
             ) AS last_delivery_id,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 2 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS last_pickup_id
      FROM locations l
     ) lh

Note the use of table aliases which make the query easier to write and to read. That said, I'm not sure you really need the delivery_id if you just want the string version:

      SELECT l.*,
             (SELECT (case when h.id = 1 then 'pending'
                           when h.id = 2 then 'active'
                      end)
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS status
      FROM locations l

Consider re-writing the inline subqueries with a CTE and window function. This avoids the use of LIMIT clauses which can have performance issues if not run optimally. This requires MySQL version 8.0+ which supports CTEs and window functions:

WITH sub AS (
  SELECT id AS last_delivery_id
       , location_id
       , haul_type_id
       , ROW_NUMBER() OVER (PARTITION BY location_id, haul_type_id
                            ORDER BY created_at DESC) AS rn
  FROM hauls
)

SELECT l.*       -- CONSIDER EXPLICITLY SELECTING COLUMNS FOR PERFORMANCE
     , CASE
          WHEN h1.last_delivery_id = 1 THEN 'pending' 
          WHEN h2.last_delivery_id = 2 THEN 'active' 
       END AS `status`
FROM locations l
LEFT JOIN sub h1
  ON h1.location_id = l.id
 AND h1.haul_type_id = 1
 AND h1.rn = 1
LEFT JOIN sub h2
  ON h2.location_id = l.id
 AND h2.haul_type_id = 2
 AND h2.rn = 1

For earlier versions (<= MySQL 5.7), integrate an aggregate query:

SELECT l.*       -- CONSIDER EXPLICITLY SELECTING COLUMNS FOR PERFORMANCE 
     , CASE
          WHEN h1.id = 1 THEN 'pending' 
          WHEN h2.id = 2 THEN 'active' 
       END AS `status`
FROM locations l
LEFT JOIN
    (SELECT location_id
          , MAX(CASE WHEN haul_type_id = 1 THEN created_at END) AS h1_max_date
          , MAX(CASE WHEN haul_type_id = 2 THEN created_at END) AS h2_max_date
     FROM hauls
     GROUP BY location_id
    ) h_agg
   ON l.location_id = h_agg.location_id
LEFT JOIN hauls h1
   ON  h1.location_id = h_agg.location_id
   AND h1.haul_type_id = 1
   AND h1.created_at = h_agg.h1_max_date
LEFT JOIN hauls h2
   ON  h2.location_id = h_agg.location_id
   AND h2.haul_type_id = 2
   AND h2.created_at = h_agg.h2_max_date

If you are running MySQL 8.0.14 or higher, you can use lateral joins for this:

select l.*, h1.*, h2.*
    case 
        when last_delivery_id = 1 then 'pending' 
        when last_delivery_id = 2 then 'active' 
    end as status
from locations l
left join lateral (
    select h.id as last_delivery_id
    from hauls h
    where h.haul_type_id = 1 and h.location_id = l.id
    order by h.created_at desc limit 1
) h1 on true
left join lateral (
    select h.id as last_pickup_id
    from hauls h
    where h.haul_type_id = 2 and h.location_id = l.id
    order by h.created_at desc limit 1
) h2 on true

Lateral joins are a powerful feature, that MySQL has been missing for long. The above statement, for example, can easily be adapted to return more columns from the each subquery.

because all the columns in select part execute all at once and last_delivery_id column dose not created yet and you try to use it , sql server return an error and for solve it you can use CTE , view and Drive table.