How to query status changes in time series?

Question

Given

create table rating (
    ID       int identity(1,1) primary key,
    PersonID int,
    Ratingdate date,
    rating   varchar(2)
);

insert into rating values ( 1, '2010-08-04' , 'A3');
insert into rating values ( 1, '2010-08-14' , 'A1');

insert into rating values ( 2, '2010-08-04' , 'G2');
insert into rating values ( 2, '2010-08-14' , 'G1');

insert into rating values ( 3, '2010-08-04' , 'G1');
insert into rating values ( 3, '2010-08-10' , 'G1');
insert into rating values ( 3, '2010-08-14' , 'G3');
insert into rating values ( 2, '2010-08-20' , 'G4');

I want a query, which returns all rows, where the rating changes for a given person. For example here I want to exclude the row ( 3, '2010-08-10' , 'G1') because the rating didn't change since ( 3, '2010-08-04' , 'G1').

I know a solution using a cursor, but I wonder, if it could be done without cursors. I tried a bit with analytical functions, but didn't found a solution.

Besides the solution, please improve the terms with which I describe the problem. I guess it is some standard task.

Edit:

Here is the link to connect asking to add the lag and lead functions to SQL Server.

Please vote.

Answer 1

It seems like you want something like

select *
  from (select person_id,
               rating_date,
               rating,
               lag(rating) over (partition by person_id
                                     order by rating_date) prior_rating
         from rating)
 where rating != prior_rating
    or prior_rating is null
 order by person_id, rating_date

which yields the following

SQL> ed
Wrote file afiedt.buf

  1  select person_id,
  2         rating_date,
  3         rating
  4    from (select person_id,
  5                 rating_date,
  6                 rating,
  7                 lag(rating) over (partition by person_id
  8                                       order by rating_date) prior_rating
  9           from rating)
 10   where rating != prior_rating
 11      or prior_rating is null
 12*  order by person_id, rating_date
SQL> /

 PERSON_ID RATING_DAT RATING
---------- ---------- --------
         1 2010-08-04 A3
         1 2010-08-14 A1
         2 2010-08-04 G2
         2 2010-08-14 G1
         2 2010-08-20 G4
         3 2010-08-04 G1
         3 2010-08-14 G3

7 rows selected.

Answer 2

Hopefully this is should work on SQL Server (tested on 2008)

WITH RatingRowNum AS
(
    SELECT
        PersonID, Ratingdate, rating, 
            ROW_NUMBER() OVER (
                PARTITION BY PersonID
                ORDER BY RatingDate) AS RowNum
    FROM 
        rating
)
SELECT
    R.PersonID, R.Ratingdate, R.rating
FROM
    RatingRowNum R
LEFT OUTER JOIN
    RatingRowNum P 
ON
    R.PersonID = P.PersonID AND
    R.RowNum = P.RowNum+1
WHERE
  R.rating <> P.Rating OR
  P.PersonID IS NULL
ORDER BY
    R.PersonID, R.Ratingdate, R.rating