Solved

how to do this logic in DB2?

Posted on 2011-09-30
7
370 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

20 Experts available now in Live!

Get 1:1 Help Now