Solved

How to solve this?

Posted on 2011-03-11
13
561 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RAISERROR WITH NOWAIT 2 24
SQL - Simple Pivot query 8 26
Convert string date and select older than 30 days from today 2 40
UPDATE JOIN multiple tables 5 14
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…

685 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