SQL Query to categorize results of separate query

I am trying to run a query on the results of a separate query to update the Route_Type column for each record based on unique Route_Code and Delivery_Day. For each distinct Route_Code from the select query, the Route_Type should be updated based on the following logic:

1. if there are no records for a specific Route_Code where Delivery_Day = ‘Mon’ then Route_Type should update to ‘A’
2. if there are no records for a specific Route_Code where Delivery_Day = ‘Sat’ then Route_Type should update to ‘B’
3. If there are no records for a specific Route_Code where Delivery_Day = ‘Mon’ OR ‘Sat’ then Route_Type should be ‘TBD’
4. Else Route_Type = ‘Both.’

Data:

Record   Route_Code   Delivery_Day   Route_Type
1        112          Mon
2        112          Thur
3        112          Sat
4        112          Sun
5        113          Tue
6        113          Fri
7        113          Sat
8        113          Sun
9        113          Wed

Select Query:

Select Distinct Route_Code
From Deliveries;

Results:

     Route_Code
1    112
2    113

The query should return all unique Route_Code and the calculated Route_Type for each, shown below…

Desired Query Result:

     Route_Code   Route_Type
1    112          Both
2    113          A

As a follow-up, I want all Route_Codes that have fewer than 1 month of records to display ‘TBD.’ The logic I have so far is

When
Max(sum(case when Delivery_Day= 'Mon' then 1 else 0 end),
    sum(case when Delivery_Day= 'Tue' then 1 else 0 end),
    sum(case when Delivery_Day= 'Wed' then 1 else 0 end),
    sum(case when Delivery_Day= 'Thu' then 1 else 0 end),
    sum(case when Delivery_Day= 'Fri' then 1 else 0 end),
    sum(case when Delivery_Day= 'Sat' then 1 else 0 end),
    sum(case when Delivery_Day= 'Sun' then 1 else 0 end)
Then 'TBD'

I realize that the native Max() function handles only single arguments, and am wondering where to go from here.

Answer

This sounds like aggregation. I find your logic a bit hard to follow. If I understand correctly, “B” has a Monday delivery and “A” has a Saturday. The logic looks like this:

select route_code,
       (case when sum(case when Delivery_Day = 'Mon' then 1 else 0 end) > 0 and
                  sum(case when Delivery_Day = 'Sat' then 1 else 0 end) > 0 
             then 'Both'
             when sum(case when Delivery_Day = 'Mon' then 1 else 0 end) > 0 
             then 'B'
             when sum(case when Delivery_Day = 'Sat' then 1 else 0 end) > 0 
             then 'A'
             else 'TBD'
        end) as route_type       
from delivies
group by route_code