Obtain the second and third position of a day, with different hours-CodePudding

Focused on entry, lunch entry and exit, company exit

Sample data:

Table

Expected result:

Expected result

Select x."ID", MIN(x."FECHA") as "1pos", null "2pos", null "3pos", MAX(x."FECHA") as "4pos"
from sirha7.v_marcaciones x 
WHERE x."CEDULA" = '0401219282' AND CAST(x."FECHA" AS date) = '2022-12-27'
GROUP BY x."ID", DATE_TRUNC ('day', x."FECHA")
ORDER BY 2 DESC

CodePudding user response：

You can use array_agg() to collect all "FECHA" ordered per "ID" in a with common table expression, then access them by their index. In case some of your "ID"s don't have 4 "FECHA"s to their name, you can use array_upper() to get the index of the last element:

with cte as 
 (  select 
      x."ID", 
      array_agg(x."FECHA" order by x."FECHA") as all_fecha_per_id
    from sirha7.v_marcaciones x 
    WHERE x."CEDULA" = '0401219282' AND CAST(x."FECHA" AS date) = '2022-12-27'
    GROUP BY x."ID", DATE_TRUNC ('day', x."FECHA")   )
select 
  y."ID",
  all_fecha_per_id[array_lower(all_fecha_per_id,1)] as "1pos",
  all_fecha_per_id[1] as "1pos",
  all_fecha_per_id[2] as "2pos",
  all_fecha_per_id[3] as "3pos",
  all_fecha_per_id[4] as "4pos",
  all_fecha_per_id[array_upper(all_fecha_per_id,1)] as "4pos"
from cte as y
ORDER BY 2 DESC;

Online demo