i have a table named as events and looks like this:
timestamp | intvalue | hostname | attributes
2019-03-13 14:43:05.437| 257 | room04 | Success 000
2019-03-13 14:43:05.317| 257 | room03 | Success 000
2019-03-13 14:43:03.450| 2049 | room05 | Error 108
2019-03-13 14:43:03.393| 0 | room05 | TicketNumber=3
2019-03-13 14:43:02.347| 0 | room04 | TicketNumber=2
2019-03-13 14:43:02.257| 0 | room03 | TicketNumber=1
The above is a sample of a table containing thousands of rows like this.
I'll explain in a few words what you see in this table. The timestamp column gives the date and time of when each event happened. In the intvalue column, 257 means successful entry, 2049 means error and 0 means a ticket made a request. The hostname gives the name of the card/ticket reader that reads each ticket and the attributes column gives some details like the number of the ticket (1, 2, 3 etc) or the type of error (i.e 108 or 109) and if the event is successful.
In this situation there is a pattern that says, if a ticket requests to enter and it is valid and happened at a time like 14:43:02.257, then the message of the successful entry will be written in the database (as a new event) in 6 seconds at most (that means at 14:49:02.257 maximum) after the ticket was read by the ticket reader.
If the ticket fails to enter, then after a time margin of 100 ms the error message will be written in the database.
So in this example what i want to do is create a table like below
timestamp | intvalue | hostname | result | ticketnumber
2019-03-13 14:43:05.437| 257 | room04 | Success 000 | TicketNumber=2
2019-03-13 14:43:05.317| 257 | room03 | Success 000 | TicketNumber=1
2019-03-13 14:43:03.450| 2049 | room05 | Error 108 | TicketNumber=3
As you can see the ticket with TicketNumber=3 is matched with the result Error 108 because if you look at the initial table, they have a time margin of less than 100ms, the other two tickets are matched 1-to-1 with their respective results, because the time margin is less than 6 seconds (and over than 100ms). You can also notice, that the hostnames can help the matching, the row with the attribute of the TicketNumber=3 has a hostname of room05, just like the next row that has the attribute of Error 108.
I've been trying to self join this table or join it with a CTE. I've used cross apply and i also have tried methods using datediff but i've failed miserably and i'm stuck.
Is there anyone that can help me and show me a correct way of achieving the desired outcome?
Thank you very much for your time.
The time lags don't really seem to make a difference, unless somehow a single room could be interleaved with both success and failure messages. Assuming that two requests do not happen in a row with no intervening event, then you can use lag():
select e.*
from (select timestamp, intvalue, hostname, attributes,
lag(attributes) over (partition by hostname order by timestamp) as ticketnumber
from event
) e
where intvalue > 0
order by timestamp