Solved

how to do this logic in DB2?

Posted on 2011-09-30
7
380 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
  • 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:Kdo
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

24 Experts available now in Live!

Get 1:1 Help Now