Change number column

Question

I have NAME and PAY, but I need CHANGEGROUP in this example:

NAME   PAY  DATE    CHANGEGROUP
Sally   12  10/01/2011  1
Sally   12  10/01/2011  1
Sally   12  11/02/2011  1
Sally   12  11/02/2011  1
Sally   12  12/01/2012  1
Sally   13  04/23/2013  2
Sally   12  04/24/2013  3
Sally   10  05/01/2013  4
Sally   10  10/01/2014  4

I tried RANK() and DENSE_RANK(), but they group according to the value - because pay goes down, it messes up my grouping. I saw this but it's not compatible with this older version of SQL 2005

Ekaterinodar

You seem to be assuming some default ordering of rows that doesn't exist. – Wellmeaning

I'm ordering my rows by the date column, but I didn't want to make a big deal about it. This database is yuck and there's nothing I can do about it. I don't want people to rip me a new one because of it and ignore my question >_< – Ekaterinodar

Answer 1

This is a gaps and islands problem.

One approach. SQL Fiddle

WITH T1
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY NAME
                    ORDER BY DATE) - ROW_NUMBER()
                                       OVER (
                                         PARTITION BY NAME, [PAY]
                                         ORDER BY DATE) AS Grp
         FROM   Table1),
     T2
     AS (SELECT *,
                MIN(DATE)
                  OVER (
                    PARTITION BY NAME, Grp) AS MinDate
         FROM   T1)
SELECT [NAME],
       [PAY],
       [DATE],
       DENSE_RANK()
         OVER (
           PARTITION BY NAME
           ORDER BY MinDate) AS CHANGEGROUP
FROM   T2
ORDER  BY NAME,
          MinDate 

Wellmeaning

There appears to be a flaw in this solution. If I use this dataset, I get Pay 10 & 12 grouped into the same CHANGEGROUP. :( insert into Table1 values ('Sally', 10, '08/28/2012') ,('Sally', 12, '09/06/2012') ,('Sally', 10, '05/17/2014') ,('Sally', 12, '01/01/2015') ,('Sally', 13, '01/01/2016'); Results... NAME PAY DATE CHANGEGROUP Sally 10 2012-08-28 1 Sally 10 2014-05-17 2 Sally 12 2012-09-06 2 Sally 12 2015-01-01 3 Sally 13 2016-01-01 4 – Blakemore

Sorry, comments don't appear to support line breaks. – Blakemore

Post another question with details of your table stucture, example data, and the query you are using. – Wellmeaning