?
Solved

How to solve this?

Posted on 2011-03-11
13
Medium Priority
?
568 Views
Last Modified: 2012-06-21
Hi,

  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.
Document.xls
0
Comment
Question by:pvsbandi
  • 6
  • 4
11 Comments
 
LVL 37

Expert Comment

by:momi_sabag
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?
0
 

Author Comment

by:pvsbandi
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.
0
 

Author Comment

by:pvsbandi
ID: 35128329
anyone? Please help.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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,
 CONSTRAINT [PK_existingset] PRIMARY KEY CLUSTERED
(
      [service_nm] ASC,
      [entry_dt] ASC,
      [client_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


then the query is

SELECT    
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.
0
 

Author Comment

by:pvsbandi
ID: 35137574
Hi,

  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?
0
 
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.
0
 

Author Comment

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

Accepted Solution

by:
Vadim Rapp earned 1500 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
0
 

Author Closing Comment

by:pvsbandi
ID: 35138617
That doesn't work like that mate...but i'll award you points for chiming in
0
 
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.
0
 

Author Comment

by:pvsbandi
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.

 
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

609 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