How to solve this?

Posted on 2011-03-11
Last Modified: 2012-06-21

  I'm on Db2 UDB 8.2
  Attached is an excel spreadsheet where i have my existing result set and the SQL that leads to the existing set.
   Below that is my desired result from the existing one.
   Basically, i want to capture any changes in the service, with the earlier one in the "earlier" column and the later one in the "later" column.

   Please help.
Question by:pvsbandi
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
  • 6
  • 4
LVL 37

Expert Comment

ID: 35109451
it is not clear how you link the rows
why the early entry for Regular Foster Care is in 2010 and not the one from 2005?

Author Comment

ID: 35109572
Hi Momi,

     The services are having service IDs, which identify the services uniquely.
     Example : Regular Foster Care - 10
                      Pre-Finalized - 12
                      Kinship Care - 15
     You are right about the Regular foster care in 2005. While compiling the desired result, i missed that change.

Author Comment

ID: 35128329
anyone? Please help.
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

LVL 40

Expert Comment

by:Vadim Rapp
ID: 35134457
if this is a table

CREATE TABLE [dbo].[existingset](
      [service_nm] [varchar](50) NOT NULL,
      [entry_dt] [datetime] NOT NULL,
      [exit_dt] [datetime] NULL,
      [client_id] [varchar](50) NOT NULL,
      [service_nm] ASC,
      [entry_dt] ASC,
      [client_id] ASC

then the query is

earlier.service_nm AS earlier,
earlier.entry_dt AS [earlier entry],
earlier.exit_dt AS [earlier exit],
later.service_nm AS [later service],
later.entry_dt AS [later entry],
later.exit_dt AS [later exit]

FROM         existingset AS earlier
LEFT OUTER JOIN existingset AS later
ON earlier.exit_dt = later.entry_dt AND earlier.client_id = later.client_id

However, this query also shows the pairs of consecutive periods of the same service, such as

Regular Foster Care       2006-08-01  / 2006-08-01  ->       Regular Foster Care      2006-08-01 2006-08-01

Merging them probably needs to be done by stored procedure, because we don't know in advance how many consecutive periods can be.

Author Comment

ID: 35137574

  But it is not always like "earlier.exit_dt = later.entry_dt"
  There are somtimes, gaps between the earlier.exit and later.entry.
  how do we deal with them?
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35137685
replace  earlier.exit_dt = later.entry_dt for something like datediff(d,earlier.exit_dt,later.entry_dt) between 0 and 10

where 10 is the number of days to qualify for consecutive service.

Author Comment

ID: 35137731
Again, if the difference is more than 10 days then it'll fail, right?
LVL 40

Accepted Solution

Vadim Rapp earned 500 total points
ID: 35137751
right, then it's new service. You can make it any number, for example 100 years - datediff(yy,earlier.exit_dt,later.entry_dt) between 0 and 100

Author Closing Comment

ID: 35138617
That doesn't work like that mate...but i'll award you points for chiming in
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35138731
If I may suggest for the future, if the solution is not complete, it's good idea to ask followup question, give more information etc., so the expert helps you further; if your question still remains unresolved, assigning points "for chiming in" is as bad idea as giving grade B to the expert who was promptly answering all your previous questions.

Author Comment

ID: 35143983
Unfortunately i have very less time to go rounds on this question. I was actually hoping a fast and efficient response which was missing this time.

   I was happy to see someone like you trying to help me and so, had awarded the points.
   Since we already have the base data and the desired results, i don't understand what additional details are required to better project this question.

   Had i more time, i would have posted what i achieved and that might have been a starting point for the subsequent advices from experts like you.


Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

626 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