SQL Query to find the next staus and get time between them

Im arguing with a sql query in bigquery, i have a table with multiple ticket numbers along with all the rest of the data from the tickets. Im trying to find out who long a ticket sits in a particular state.

My data looks like this:

TicketNumber | DateUpdated | State | Notes | …..

Im trying to get this:

TicketNumber | DateUpdated | State | NextState | DateChanged

Ive been playing with various different queries but i always seem to get duplicates. like this one:

SELECT distinct allinc.TicketNumber as inc, allinc.state, DateChanged, allinc.TicketNumber, nextupdate
FROM `incidents` allinc
left outer join (
select *, CAST(DateUpdated AS datetime) as DateChanged, state as DateChanged from `incidents`
) resinc
on allinc.TicketNumber = resinc.TicketNumber and CAST(resinc.DateChanged AS datetime) > CAST(allinc.TicketNumber AS datetime) 
where allinc.state = 'Awaiting User' 
order by inc

The end goal is to get the total time the ticket was set to ‘Awaiting User’ but this can be multiple times for each ticket.

Any advice would be great.

Answer

I think you are describing lead():

select i.*,
       lead(state) over (partition by ticketnumber order by dateupdated) as next_state,
       lead(dateupdated) over (partition by ticketnumber order by dateupdated) as next_dateupdated
from incidents i;

Advertisement

Answer