CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Niek
Niek

Reputation: 1030

Check for sequence of rows

I have a table from a ticketing system, that is formatted something like this (only rows for two sample tickets shown):

-----------------------------------------
| commentid |  ticketid  | ticketstatus |
|-----------|------------|--------------|
|    105    |    1337    |      TBQ     |
|    109    |    4242    |      TBP     |
|    118    |    1337    |      TBQ     |
|    121    |    4242    |    closed    |
|    125    |    1337    |   finished   | 
|    176    |    1337    |    closed    |
-----------------------------------------

Where ticketid is the unique ID of a ticket, commentid the index which is incremented for every new comment and ticketstatus is the status of the ticket.

Now, I would like to count the number of tickets, where the status changed directly from TBQ to finished or directly from TBQ to closed.

I think this should be possible with a recursive CTE, but stuff gets more complicated because commentid is not a direct sequence for one ticket but rather for all tickets. Additionally, there can be quite a number of rows between status changes of a single ticket.

The query should return "1" for the example table above, since ticket 1337 is the only ticket that changes state directly from TBQ to closed

How would I do this?

Upvotes: 1

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

What you really want is lag(), but that is not available in SQL Server 2008. You can use apply instead:

select c.*, cprev.*
from comments c cross apply
     (select top 1 cprev.*
      from comments cprev
      where cprev.ticketid = c.ticketid and
            cprev.commentid < c.commentid
      order by cprev.commentid desc
     ) cprev
where cprev.ticketstatus = 'TBQ' and
      c.ticketstatus in ('finished', 'closed');

If you want a count of such tickets then use select count(*) or select count(distinct c.ticketid) (if this can happen more than one time for a given ticket).

Upvotes: 3

Related Questions