0

So, the table setup is something like this:

table: ticket(ticket_id, ...)

table: ticket_status_history(status_history_id, ticket_id, ticket_status, ticket_status_datetime)

The default ticket_status is OPEN, and the first ticket status that I'm interested in is ACKNOWLEDGED.

So, the idea is that a specific ticket has a set of ticket_status_history events, each recorded in the separate table. Each ticket status entry points to its corresponding ticket, ticket_id is a foreign key.

Now, a ticket can actually be created directly in ACKNOWLEDGED so it would get a corresponding entry directly in ACKNOWLEDGED, without ever being in OPEN. But many of them will go OPEN -> ACKNOWLEDGED -> ...

What I'd like to do would be to determine for each ticket the time interval between ticket creation (OPEN) and ticket acknowledgment (ACKNOWLEDGE), but if the state is directly ACKNOWLEDGE, set the time difference as a default of 0 (because the ticket was created directly in this state).

Is this doable in SQL, for PostgreSQL? I'm a bit stumped at the moment. I found this: Calculate Time Difference Between Two Rows but it's for SQL Server, instead, plus I'm not sure how the default value could be included.

The end state would actually be aggregating the time differences and computing an average duration, but I can't figure out the first step

oblio
  • 1,519
  • 15
  • 39

1 Answers1

2

Your query could look like this:

SELECT t.*,coalesce(ack.ticket_status_datetime - op.ticket_status_datetime 
                    ,'0'::interval) AS op_ack_diff
FROM ticket t
LEFT JOIN ticket_status_history ack ON(t.ticket_id = ack.ticket_id
                                       AND ack.ticket_status = 'ACKNOWLEDGED')
LEFT JOIN  ticket_status_history op ON(t.ticket_id = op.ticket_id
                                       AND op.ticket_status = 'OPENED')
WHERE t.ticket_id = x;

The difference of the timestamps yields null if one of the entries is missing. The coalesce function will return its second argument in this case.

clamp
  • 2,552
  • 1
  • 6
  • 16
  • Great answer. The thing is, if ACK is null but OP is not, the correct difference would be `current_time` - OP, since technically the ticket isn't yet in ACK. I wonder how this can be handled? Because 0 would be a lie :) – oblio Feb 04 '21 at 23:00
  • Found it, I can use `case` instead of coalesce, have more control with that. But your approach was the base for the solution, thanks a lot! – oblio Feb 05 '21 at 07:44
  • 1
    Replace ACK time with current_time if it is null: `coalesce(coalesce(ack.ticket_status_datetime,current_time) - op.ticket_status_datetime,0::interval)` – clamp Feb 05 '21 at 08:01