Question 19117 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

How to improve the query to result the data in one row? – Amazon Web Sevices

The following redshift query is showing the data as screenshot in below:

select 
     distinct id_butaca,
        max(CASE WHEN id_tipoorden = 1 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_compra,
        max(CASE WHEN id_tipoorden = 4 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_devo
from dw_fact_table 
where 
id_butaca = 175044501

enter image description here

How can I remove the empty values and put the values in the same row?

id_butaca,   max_fecha_compra,       max_fecha_devo
175044501   2023-01-09 12:11:04.0   2023-01-09 12:09:55
Tags:

2

Answers


  1. 0

    Maybe something like this?

    select  id_butaca,
        max(CASE WHEN id_tipoorden = 1 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_compra,
        max(CASE WHEN id_tipoorden = 4 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_devo
from dw_fact_table 
where id_butaca = 175044501
group by id_butaca
    Login or Signup to reply.
  2. 0

    This will help you to merge two rows in one row.

    select id_butaca,
    max(max_fecha_compra) as max_fecha_compra,
    max(max_fecha_devo) as max_fecha_devo
from (
        select distinct id_butaca,
            max(
                CASE
                    WHEN id_tipoorden = 1 THEN fechahoracompra
                    ELSE to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                END
            ) over (partition by id_butaca, id_tipoorden) as max_fecha_compra,
            max(
                CASE
                    WHEN id_tipoorden = 4 THEN fechahoracompra
                    ELSE to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                END
            ) over (partition by id_butaca, id_tipoorden) as max_fecha_devo
        from dw_fact_table
        where id_butaca = 175044501
    )
group by id_butaca
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search