Obtain Name Column Based on Value-CodePudding

I have a table that calculates the number of associated records that fit a criteria for each parent record. See example below:

note - morning, afternoon and evening are only weekdays

| id | morning | afternoon | evening | weekend |
| -- | ------- | --------- | ------- | ------- |
|  1 |    0    |     2     |    3    |    1    |
|  2 |    2    |     9     |    4    |    6    |

What I am trying to achieve is to determine which columns have the lowest value and get their column name as such:

| id | time_of_day |
| -- | ----------- |
|  1 |  morning    |
|  2 |  afternoon  |

Here is my current SQL code to result in the first table:

SELECT 
    leads.id,
    COALESCE(morning, 0) morning,
    COALESCE(afternoon, 0) afternoon,
    COALESCE(evening, 0) evening,
    COALESCE(weekend, 0) weekend
FROM leads
LEFT OUTER JOIN (
    SELECT DISTINCT ON (lead_id) lead_id, COUNT(*) AS morning
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 0 AND extract('hour' from created_at) < 12)
    GROUP BY lead_id
) morning ON morning.lead_id = leads.id
LEFT OUTER JOIN (
    SELECT DISTINCT ON (lead_id) lead_id, COUNT(*) AS afternoon
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 12 AND extract('hour' from created_at) < 17)
    GROUP BY lead_id
) afternoon ON afternoon.lead_id = leads.id
LEFT OUTER JOIN (
    SELECT DISTINCT ON (lead_id) lead_id, COUNT(*) AS evening
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 17 AND extract('hour' from created_at) < 25)
    GROUP BY lead_id
) evening ON evening.lead_id = leads.id
LEFT OUTER JOIN (
    SELECT DISTINCT ON (lead_id) lead_id, COUNT(*) AS weekend
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (6,7)
    GROUP BY lead_id
) weekend ON weekend.lead_id = leads.id

CodePudding user response：

You can use CASE/WHEN/ELSE to check for the specific conditions and produce different values. For example:

with
q as (
  -- your query here
)
select
  id,
  case
    when morning <= least(afternoon, evening, weekend) then 'morning'
    when afternoon <= least(morning, evening, weekend) then 'afternoon'
    when evening <= least(morning, afternoon, weekend) then 'evening'
    else 'weekend'
  end as time_of_day
from q