CODEWITHSUNDEEP
amazon-redshiftcase
nomnom3214
nomnom3214

Reputation: 259

how to divide string in a column to be a column name in redshift

I have built this query below in order to get the total vol by each route, sla_min and also the sla_status.

The sla_status is calculated with case when syntax to get the over sla and also meet sla

with data_manifest as (
select no,
       concat(concat(origin,'-'),destination) as route_city, 
       sla_min,
       case
           when status>0 and datediff(day, sla_max_date_time_internal, last_valid_tracking_date_time) > 0 then 'OVER SLA'
           when status=0 and datediff(day, sla_max_date_time_internal, current_date) > 0 then 'OVER SLA' else 'MEET SLA'
        end as status_sla
from data
where trunc(tgltransaksi::date) between ('30 January,2023') and ('9 February,2023')
), data_vol as (
select
    route_city,
    count(distinct no) as volume,
    status_sla,
    sla_min,
from data_manifest
group by route_city, status_sla, sla_min
)

The query results this:

route_city     vol      status_sla      sla_min
A - B          20        MEET SLA          2
A - B          40        OVER SLA          2
B - C          30        MEET SLA          1
B - C          30        OVER SLA          1

my question is how can I split the MEET SLA and OVER SLA become the column names so the structure would be like this:

route_city    MEET SLA   OVER SLA   total_vol    sla_min
A - B          20           40         60           2
B - C          30           30         60           1

how should I write the query to get the desired result in redshift?

thank you in advance

Upvotes: 0

Views: 20

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

Not seeing your input data it isn't clear what exactly you need but here's a shot.

You need to stop grouping by status_sla and count the number for each value of status_sla.

with data_manifest as (
select no,
       concat(concat(origin,'-'),destination) as route_city, 
       sla_min,
       case
           when status>0 and datediff(day, sla_max_date_time_internal, last_valid_tracking_date_time) > 0 then 'OVER SLA'
           when status=0 and datediff(day, sla_max_date_time_internal, current_date) > 0 then 'OVER SLA' else 'MEET SLA'
        end as status_sla
from data
where trunc(tgltransaksi::date) between ('30 January,2023') and ('9 February,2023')
), data_vol as (
select
    route_city,
    count(distinct no) as volume,
    count(distinct decode(status_sla, 'MEET SLA', no, NULL)) as meet_sla,
    count(distinct decode(status_sla, 'OVER SLA', no, NULL)) as over_sla,
    sla_min,
from data_manifest
group by route_city, sla_min
)

There are other ways of doing this that might work betting for the edge cases. Not knowing what these are results in this minimal change approach.

Above code is untested.

Upvotes: 1

Related Questions