Hello,

We have a table of events. Each event has a date, is associated with an object, and has a type. So the table is like :

eventsTable (eventUniqueKey, objectID, EventDate, eventType)

We want a select that lists all the objectID that have an event of eventType 'X' without an eventType 'Y' __after__.

To make things clear :

eventUniqueKey / objectID / EventDate / eventType

1 / 1 / 2016-01-01 / X

2 / 1 / 2016-01-02 / Y

In this case, object 1 has X and an Y after, so it must not be pulled out.

1 / 1 / 2016-01-01 / X

2 / 2 / 2016-01-02 / Y

In this case, objet 1 has and X event but no Y event (Y event is to object 2), so objectID 1 must be pulled out, and not objectID 2.

1 / 1 / 2016-01-02 / X

2 / 1 / 2016-01-01 / Y

In this case, object 1 has X but the Y is before, so objectID 1 must be pulled out.

We ask for a 'pure' SQL that does that, we did it with temp table and scalar function, etc. but it is pretty slow.

Thanks for help

In any event, this is one way to get the rows you've identified in your 3 scenarios:

Given the following data set in #tmp_table:

Rows 1 and 6 are a valid pair. Rows 2 and 4 are invalid X events.

Open in new window

The basic premise is to assign row numbers based on the original id and date ASC and then also DESC, and then look for the invalid scenarios: where the row number for ASC and DESC is the same on an X event, or where the X event occurs before the Y event.

Result: