CODEWITHSUNDEEP
sqlpostgresql
xCloudx8
xCloudx8

Reputation: 721

How to put in the same row different values

this is my table:

Event           Time    Order           Mission         TimeStamp
arrived         14:50   281475024521271 281475024568005 2017-08-09 14:50:59
nextDelivery    14:51   281475024521271 281475024568005 2017-08-09 14:51:33
arrived         15:27   281475024521271 281475024568005 2017-08-09 15:27:13
nextDelivery    15:28   281475024521271 281475024568005 2017-08-09 15:28:04

And this is my query:

with times as(
    select 
        event,
        to_char("timestamp"::timestamptz at time zone 'Europe/Rome', 'HH24:MI') as Time,
        orders,
        mission,
        "timestamp"::timestamptz at time zone 'Europe/Rome' as TimeStamp
    from main_source_execevent
    where orders = '281475024521271'
    and event in ('arrived', 'nextDelivery')
    order by "timestamp"
)
    select 
        case when event = 'arrived' then time end as TimeIN,
        case when event = 'nextDelivery' then time end as TimeOUT,
        orders  
    from Times

And the query result is:

    TimeIN   TimeOut  Order
    14:50    NULL     281475024521271
    NULL     14:51    281475024521271
    15:27    NULL     281475024521271
    NULL     15:28    281475024521271

What i'm trying to achieve is to have the timein and timeout on the same row for the order. I cannot figure out how to grab this result. Can you help me?

Desired result:

 TimeIN   TimeOut  Order
 14:50    14:51     281475024521271
 15:27    15:28     281475024521271

EDIT: ADDED INFO

The join should be with the closest timestamp between arrived and nextdelivery so there can be only one couple of timein/timeout as described in the desired result.

Upvotes: 0

Views: 48

Answers (2)

Grzegorz Grabek
Grzegorz Grabek

Reputation: 980

I assume that order and mission value are important and should be equal on both sides of the join. Distinct on returns first row for unique values from columns inside brackets. If you remove line "and mse1.order = 281475024521271" and will return result for all order/missions

select distinct on (mse1.order, mse1.timestamp)
       mse1.timestamp time_in, 
       max(mse2.timestamp) time_out,
       mse1.order
  from main_source_execevent mse1
       main_source_execevent mse2
 where mse1.order = mse2.order 
   and mse1.mission = mse2.mission
   and mse1.event = 'arrived'
   and mse2.event = 'nextDelivery' 
   and mse1.order = 281475024521271
 order by mse1.order, mse1.timestamp, mse2.timestamp

Upvotes: 0

user330315
user330315

Reputation:

Note: this answers the original version of the question, not the new, extended one. Although it's just a partial answer, I'll keep it around until a better one is added.

Use a self join where the join condition contains the value for the "time out" event and limit the main table to the "time in" events:

select tin."order", 
       tin."timestamp" as time_in, 
       tout."timestamp" as time_out
from main_source_execevent tin 
  left join main_source_execevent tout on tin."order" = tout."order" and tout.event = 'nextDelivery'
where tin.event = 'arrived'  
  and tin.order = 281475024521271

Online example: http://rextester.com/QMZO32570

With an index on ("order", event) this should be quite efficient

Upvotes: 2

Related Questions