Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to solve this?

Posted on 2011-03-11
13
Medium Priority
?
567 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
13 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
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

916 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