Looking at the previous - next values in a table

Posted on 2011-10-14
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
    LVL 37

    Expert Comment

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

    Author Comment

    with a procedure..
    LVL 37

    Accepted Solution

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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now