Link to home
Start Free TrialLog in
Avatar of pvsbandi
pvsbandiFlag for United States of America

asked on

Looking at the previous - next values in a table

Hi,
   I admit, had asked a similar question in the past, but lthis time, looking for a procedural approach to achieve it.
   I have a table like this.
   ID                       Start Dt                       End Date
 123                       01/01/2009                 02/03/2009
 123                       04/01/2009                 05/03/2009
 123                       05/03/2009                 06/20/2009
 123                       06/30/2009                 08/04/2009
 123                       08/04/2009                 

Open in new window

A blank in the End Date means that the episode is still active.
The output should be like this.

  ID                           Start Dt                       End Date
 123                       01/01/2009                 02/03/2009
 123                       04/01/2009                 

Open in new window


Logic looks at the End Date of the first row and the Start Date of the second row.
 If they are not the same, then that row should be printed as is.
 If they both are the same, then it should capture the start date as the start date, when this pattern started happening (in our case, it is 04/01/2009) and continue to look for any such chain.
 If it is continuous, then the last end date following the pattern should be captured.
 Hope i'm making sense here.

 I tried the recursive SQL, but didn't work as expected.
Any insight into this would be a great help.

Thank You!
 
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

do you need to solve it with a query or with a procedure?
Avatar of pvsbandi

ASKER

with a procedure..
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks so much! I thought i had accepted this solution long back!