I need a way to group data based on previous rows

Question

Let me try to explain this again.

This table has a record for each person for each day of the month. There are approx 20 fields in the table. If any of the fields change (other than the date fields), then I want to group those records. So, for example, if days 1, 2, & 3 are the same, then when I read in day 4 and notice that it is changed, I want to group days 1, 2, & 3 together with a begindate of day one, and an enddate of day 3...etc

Rownum  ID BegDate  EndDate   Field1, Field2.... Field20
 1      1  6/1/2017 6/1/2017  xxxx    xxxx        xxxxx
 2      1  6/2/2017 6/2/2017  xxxx    xxxx        xxxxx
 3      1  6/3/2017 6/3/2017  xxxx    xxxx        xxxxx
 4      1  6/4/2017 6/4/2017  yyyy    yyyy        yyyy
 5      1  6/5/2017 6/5/2017  yyyy    yyyy        yyyy
 6      1  6/6/2017 6/6/2017  xxxx    xxxx        xxxxx
 7      1  6/7/2017 6/7/2017  xxxx    xxxx        xxxxx
 8      1  6/8/2017 6/8/2017  zzzz    zzzz        zzzz
....

So in the example data above, I would have a group with rows 1,2,3 then a group with rows 4,5 then a group with rows 6,7 then a group with 8...etc

ID  BegDate    EndDate  Field1  Field2 ...... Field20   Sum
1   6/1/2017   6/3/2017  xxxx    xxxx          xxxxx      3
1   6/4/2017   6/5/2017  yyyy    yyyy          yyyy       2
1   6/6/2017   6/7/2017  xxxx    xxxx          xxxxx      2
1   6/8/2017   6/15/2017 zzzz    zzzz          zzzz       8
.....

Possible duplicate of [Grouping and counting rows by value until it changes](https://stackoverflow.com/questions/29327566/grouping-and-counting-rows-by-value-until-it-changes) — Tab Alleman, Nov 28 '17 at 19:20
AS @GordonLinoff said, please provide sample data and desired resulting data set. The solution will likely be a windowed function. — Andrew O'Brien, Nov 28 '17 at 19:20

score 0 · Answer 1 · answered Nov 28 '17 at 19:59

As example. Create table:

 create table t
 (date_ datetime,
  status varchar(1));

And add data

  insert into t values ('2017-11-01','A');
  insert into t values ('2017-11-02','A');
  insert into t values ('2017-11-03','A');
  insert into t values ('2017-11-04','B');
  insert into t values ('2017-11-05','B');
  insert into t values ('2017-11-06','B');
  insert into t values ('2017-11-07','C');
  insert into t values ('2017-11-08','C');
  insert into t values ('2017-11-09','C');
  insert into t values ('2017-11-10','C');
  insert into t values ('2017-11-11','B');
  insert into t values ('2017-11-12','B');
  insert into t values ('2017-11-13','B');
  insert into t values ('2017-11-14','B');
  insert into t values ('2017-11-15','B');

And use this query

 select min(date_start),
           IFNULL(date_end,now()),
           status
    from 
    ( select 
      t1.date_ date_start,
      (select min(date_) from t t2 where t2.date_>t1.date_ and t2.status<>t1.status) - interval 1 day as 'date_end',
     t1.status status
    from t t1
      ) a
      group by date_end,status
      order by 1

http://sqlfiddle.com/#!9/96e27/11

score 0 · Answer 2 · answered Nov 29 '17 at 02:36

You can do this with a difference of row numbers:

select ID, min(BegDate) as Begdate, max(EndDate) as max(EndDate),
       Field1, Field2, ...... Field20,
       datediff(day, min(BegDate), max(EndDate))
from (select t.*,
             row_number() over (partition by id order by begdate) as seqnum,
             row_number() over (partition by id, Field1, Field2, . . ., Field20 order by begdate) as seqnum_2
      from t
     ) t
group by id, (seqnum - seqnum_2), Field1, Field2, . . . Field20 ;

score 0 · Answer 3 · answered Nov 29 '17 at 04:25

try below query (with 2 extra fields - field1 and field2). To handle you 20 fields add remaining column whereever you see field1,field2 with field1, field2, field3,......field20

    create table #tmp (RowNum int, id int,begdate datetime,EndDate datetime, field1 varchar(10),field2 varchar(10))

    insert into #tmp values(1,1,'2017-06-01','2017-06-01','xxxxx','xxxxx')
    insert into #tmp values(2,1,'2017-06-02','2017-06-02','xxxxx','xxxxx')
    insert into #tmp values(3,1,'2017-06-03','2017-06-03','xxxxx','xxxxx')
    insert into #tmp values(4,1,'2017-06-04','2017-06-04','yyyyy','yyyyy')
    insert into #tmp values(5,1,'2017-06-05','2017-06-05','yyyyy','yyyyy')
    insert into #tmp values(6,1,'2017-06-06','2017-06-06','xxxxx','xxxxx')
    insert into #tmp values(7,1,'2017-06-07','2017-06-07','xxxxx','xxxxx')
    insert into #tmp values(8,1,'2017-06-08','2017-06-08','zzzzz','zzzzz')
    insert into #tmp values(9,1,'2017-06-09','2017-06-09','zzzzz','zzzzz')
    insert into #tmp values(10,1,'2017-06-10','2017-06-10','zzzzz','zzzzz')

    insert into #tmp values(11,2,'2017-06-04','2017-06-04','yyyyy','yyyyy')
    insert into #tmp values(12,2,'2017-06-05','2017-06-05','yyyyy','yyyyy')
    insert into #tmp values(13,2,'2017-06-06','2017-06-06','xxxxx','xxxxx')
    insert into #tmp values(14,2,'2017-06-07','2017-06-07','xxxxx','xxxxx')


    insert into #tmp values(15,1,'2017-06-11','2017-06-11','xxxxx','xxxxx')
    insert into #tmp values(16,1,'2017-06-12','2017-06-12','xxxxx','xxxxx')
    insert into #tmp values(17,1,'2017-06-13','2017-06-13','zzzzz','xxxxx')
    insert into #tmp values(18,1,'2017-06-14','2017-06-14','zzzzz','xxxxx')
    insert into #tmp values(19,1,'2017-06-15','2017-06-15','yyyyy','xxxxx')
    insert into #tmp values(20,1,'2017-06-16','2017-06-16','zzzzz','xxxxx')


    select ID, min(BegDate) as Begdate, max(EndDate) as EndDate,
           Field1,Field2, /*Add all other fields here*/
           datediff(day, min(BegDate), max(EndDate))+1 As [Sum]
    from(
    select *,
                 row_number() over (partition by id order by begdate) as seqnum,
                 row_number() over (partition by id, Field1,field2 /*Add all other fields here*/ order by begdate) as seqnum_2
          from #tmp

        ) t
    group by id, (seqnum - seqnum_2), Field1,Field2 /*Add all other fields here*/
    order by ID,Begdate


    Drop table #tmp

I need a way to group data based on previous rows

3 Answers3