Algorithm to find start and end dates

Database

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)

Tags (5)
2 REPLIES
Teradata Employee

Re: Algorithm to find start and end dates

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_dateas 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

Re: Algorithm to find start and end dates

Thank you so much!