Niek Niek - 3 years ago 92
SQL Question

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?

Answer Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download