Solved

How to solve this?

Posted on 2011-03-11
13
557 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

707 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

19 Experts available now in Live!

Get 1:1 Help Now