Solved

how to do this logic in DB2?

Posted on 2011-09-30
7
420 Views
Last Modified: 2012-05-12
Hi,

  I have a table with the structure like this:
ID	C_ID	START_DT	END_DT
1                    123              08/01/2009	08/31/2009
2	123	09/01/2009  10/01/2010
3	123	10/01/2010	11/30/2010
4	123	11/30/2010	
5	345	09/01/2009	10/01/2010
6	345	10/01/2010	11/01/2010
7	456	10/15/2010

The result set i want is :

C_ID	START_DT	END_DT
123	09/01/2009	
345	09/01/2009	11/01/2010
456	10/15/2010	

Open in new window


lOGIC IS :       fOR C_ID = 123 and ID = 2,the end_dt of 10/01/2010, is the start date for ID = 3.
            Again, for the same C_ID of 123 and ID = 3, the end date of 11/30/2010 is the start date for ID = 4.
 In this case, i want to show the first start id, which is 09/01/2009 and the last end date, which is blank.

 Similarly, for C_ID = 345, if the end date and the start date match consistently, then the first start date and the last end date should display.
0
Comment
Question by:pvsbandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36893360
there is no simple way of doing this
it is either a recursive sql statement, or a stored procedure
which version and platform are you using?
0
 

Author Comment

by:pvsbandi
ID: 36893371
Recursive SQL is what i'm looking for.
  DB2 8.2 and the platform is Mainframes
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36893838
Hi pvsbandi,

Do you need to "walk" the date chains for each C_ID or is there a data relationship that for each C_ID, the row with the lowest ID value has the start date, or the row with the highest ID value has the highest start date (and the end date that you want)?

Walking through this recursively is possible, but it can get very complicated if dates don't increment.  If the dates can repeat or, worse yet, appear out of order within a chain, it could be difficult to structure a query to stop at the correct row.

On the other hand, if you can accept the lowest start date for the C_ID value, and the end date of the row with the highest start date, the query just requires a couple of joins and aggregation.



Kent
0
Stressed Out?

Watch some penguins on the livecam!

 

Author Comment

by:pvsbandi
ID: 36893891
Hi Kent,

   Yes, the lowest Start Date and the Highest end Date is what is required. But it should be picked, only when the chain has the previous end date as latter's start date..
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36893915

Momi's correct then, that recursive SQL is required to verify the integrity of the chain.  Note that the recursive SQL will return the highest end date contained within the chain, not necessarily the highest end date for the C_ID.

It appears that the chain requires that the end date of a row equal the start date of the next row.  Can there be two rows for the same C_ID with the same start date or end date?  If so, we need to determine which should be followed.  I assume that the dates can never decrement within the chain?


Kent
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 36893993

Try the query below.  It should be pretty close.  :)

Just replace {mytable} with your table name.



Kent

WITH x (C_ID, length, start_dt, end_dt)
AS
(
  SELECT C_ID, 1, min(start_dt) over (C_ID), end_dt
  FROM {table}
  
  UNION ALL
  
  SELECT t0.C_ID, t0.length+1, t0.start_date, t1.
  FROM x t0
  INNER JOIN {mytable} t1
    ON t0.C_ID = t1.C_ID
   AND t0.end_date = t1.start_date
  WHERE t1.start_date is no null
)
SELECT C_ID, start_dt, end_dt
FROM x t2
WHERE x.length = (SELECT max(length) FROM x t3 where t3.C_ID = t2.ID);

Open in new window

0
 

Author Closing Comment

by:pvsbandi
ID: 36905563
Thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

690 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