CODEWITHSUNDEEP
sqlsql-servergroup-bypartition-by
Yonabout
Yonabout

Reputation: 5

SQL server group / partition to condense history table

Got a table of dates someone was in a particular category like this:

    drop table if exists #category
    create table #category (personid int, categoryid int, startdate datetime, enddate datetime)
    insert into #category 
    select * from 
    (
    select 1 Personid, 1 CategoryID, '01/04/2010' StartDate, '31/07/2016' EndDate union
    select 1 Personid, 5 CategoryID, '07/08/2016' StartDate, '31/03/2019' EndDate union
    select 1 Personid, 5 CategoryID, '01/04/2019' StartDate, '01/04/2019' EndDate union
    select 1 Personid, 5 CategoryID, '02/04/2019' StartDate, '11/08/2019' EndDate union
    select 1 Personid, 4 CategoryID, '12/08/2019' StartDate, '03/11/2019' EndDate union
    select 1 Personid, 5 CategoryID, '04/11/2019' StartDate, '22/03/2020' EndDate union
    select 1 Personid, 5 CategoryID, '23/03/2020' StartDate, NULL EndDate union
    select 2 Personid, 1 CategoryID, '01/04/2010' StartDate, '09/04/2015' EndDate union
    select 2 Personid, 4 CategoryID, '10/04/2015' StartDate, '31/03/2018' EndDate union
    select 2 Personid, 4 CategoryID, '01/04/2018' StartDate, '31/03/2019' EndDate union
    select 2 Personid, 4 CategoryID, '01/04/2019' StartDate, '23/06/2019' EndDate union
    select 2 Personid, 4 CategoryID, '24/06/2019' StartDate, NULL EndDate 
    ) x
    order by personid, startdate

I'm trying to condense it so I get this:

PersonID categoryid startdate EndDate
1 1 01/04/2010 31/07/2016
1 5 07/08/2016 11/08/2019
1 4 12/08/2019 03/11/2019
1 5 04/11/2019 NULL
2 1 01/04/2010 09/04/2015
2 4 01/04/2015 NULL

I'm having issues with people like personid 1 where they are in (e.g.) category 5, then go into category 4 and them back into category 5.

So doing something like:

select
personid,
categoryid,
min(startdate) startdate,
max(enddate) enddate
from #category
group by 
personid, categoryid

gives me the earliest date from category 5's first period, and the latest date from the second period - and means it creates an overlapping period.

So I tried partitioning it with a rownum or rank, but it still does the same thing - i.e. treats the 'category 5's as the same group:

select
rank() over (partition by personid, categoryid order by personid, startdate) rank,
c.*
from #category c
order by personid, startdate
rank personid categoryid startdate enddate
1 1 1 2010-04-01 00:00:00.000 2016-07-31 00:00:00.000
1 1 5 2016-08-07 00:00:00.000 2019-03-31 00:00:00.000
2 1 5 2019-04-01 00:00:00.000 2019-04-01 00:00:00.000
3 1 5 2019-04-02 00:00:00.000 2019-08-11 00:00:00.000
1 1 4 2019-08-12 00:00:00.000 2019-11-03 00:00:00.000
4 1 5 2019-11-04 00:00:00.000 2020-03-22 00:00:00.000
5 1 5 2020-03-23 00:00:00.000 NULL
1 2 1 2010-04-01 00:00:00.000 2015-04-09 00:00:00.000
1 2 4 2015-04-10 00:00:00.000 2018-03-31 00:00:00.000
2 2 4 2018-04-01 00:00:00.000 2019-03-31 00:00:00.000
3 2 4 2019-04-01 00:00:00.000 2019-06-23 00:00:00.000
4 2 4 2019-06-24 00:00:00.000 NULL

You can see in the rank column that the category 5's start off 1,2,3, miss a row and carry on 4, 5 so obvs in the same partition - I thought that adding the order by clause would force it to start a new partition when the category changed from 5 to 4 and back again.

Any thoughts?

Upvotes: 0

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a type of gaps and islands problem. However, if your data tiles perfectly (no gaps) as it does in your example data, then you can do this without any aggregation at all -- which should be the most efficient method:

select personid, categoryid, startdate,
       dateadd(day, -1, lead(startdate) over (partition by personid order by startdate)) as enddate
from (select c.*,
             lag(categoryid) over (partition by personid order by startdate) as prev_categoryid
      from #category c
     ) c
where prev_categoryid is null or prev_categoryid <> categoryid;

The where clause only selects the rows where the category changes. The lead() then gets the next start date -- and subtracts 1 for your desired enddate.

Upvotes: 1

Related Questions