[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Looking at the previous - next values in a table

Posted on 2011-10-14
Medium Priority
Last Modified: 2012-05-12
   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!
Question by:pvsbandi
  • 2
  • 2
LVL 37

Expert Comment

ID: 36969811
do you need to solve it with a query or with a procedure?

Author Comment

ID: 36970036
with a procedure..
LVL 37

Accepted Solution

momi_sabag earned 2000 total points
ID: 36970108
that should be something like

                                  ,start_dt timestamp
                                  ,end_dt timestamp)

declare cursor c1 for
  select id, start_dt, end_dt
   from my_table
   order by id, start_dt;

open c1;

set v_last_id = -1;

fetch c1 into : v_id, v_start_dt, v_end_dt;
set v_slot_start = v_start_dt
set v_last_end_dt = v_end_dt

while (sqlcode = 0)
   -- check for new id
   if (v_last_id <> v_id) then
     -- insert the previous record
     insert into session.abc values( v_last_id, v_slot_start, v_slot_end);
      -- check for new episode
      if (v_start_dt <> v_last_end_dt) then
            insert into session.abc values( v_id, v_slot_start, v_last_end_dt);

        set    v_slot_start = v_start_dt

     set v_last_id = v_ld
     set v_last_end_dt = v_end_dt
fetch c1 into ...
end loop

Author Closing Comment

ID: 37009834
Thanks so much! I thought i had accepted this solution long back!

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question