All Forums Database
machinegusto 2 posts Joined 11/13
04 Nov 2013
Algorithm to find start and end dates

Hi All,
Given the following data sorted by Name and Date:
Name      Status      Date
Bob        A           2013-08-01
Bob        A           2013-08-04
Bob        C           2013-09-13
Sally       A           2013-04-17
Sally       C           2013-06-07
Sally       C           2013-07-09
How do I find the start and end dates for when a person has a particular status? So that the output looks like this:
Name    Status       Start_Date          End_Date
Bob        A             2013-08-01       2013-09-12
Bob        C             2013-09-13      (Today's Date)
Sally       A             2013-04-17       2013-06-06
Sally       C             2013-06-07      (Today's Date)
 
 

VBurmist 96 posts Joined 12/09
05 Nov 2013

Hello,
you can try the following two-steps algorithm:
1) eliminate rows that have the same status that the "previous" row (for the same name)
2) make the start_date and end_dates for the remaining rows.
SQL:
 
create table vlad.test1 (
name1 varchar(200),
status char(1),
thedate date)
;
insert into vlad.test1 ('Bob','A',date'2013-08-01');
insert into vlad.test1 ('Bob','A',date'2013-08-04');
insert into vlad.test1 ('Bob','C',date'2013-09-13');
insert into vlad.test1 ('Sally','A',date'2013-04-17');
insert into vlad.test1 ('Sally','C',date'2013-06-07');
insert into vlad.test1 ('Sally','C',date'2013-07-09');
 
 
select name1, status, thedate as start_date,
           coalesce( max(thedate) over (partition by name1 order by thedate rows between 1 following and 1 following) - 1
                           , current_date)  as end_date
from (
    select max(status) over (partition by name1 order by thedate rows between 1 preceding and 1 preceding) as prev_status, a.*
     from vlad.test1 as a
  qualify  prev_status is NULL OR prev_status <> status
) as t
order by 1,2,3,4
 
name1 status start_date end_date

1 Bob A 8/1/2013 9/12/2013

2 Bob C 9/13/2013 11/5/2013

3 Sally A 4/17/2013 6/6/2013

4 Sally C 6/7/2013 11/5/2013

 

 

machinegusto 2 posts Joined 11/13
05 Nov 2013

Thank you so much!

You must sign in to leave a comment.