Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

grouping sequenced data

Posted on 2009-07-08
6
Medium Priority
?
232 Views
Last Modified: 2013-11-05
From the table

Start_date    end_date  value
2009/01/01  2009/30/04   A
2009/01/05  2009/31/06   A
2009/01/06  2009/31/08   B
2009/01/09  2009/31/12   A

I need the table
2009/01/01  2009/31/05   A
2009/01/06  2009/31/08   B
2009/01/09  2009/31/12   A

Werner

0
Comment
Question by:WAeberhardt
[X]
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 Comments
 

Expert Comment

by:cjonline
ID: 24801869
Can you explain the ordering?
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24801876
I doubt this will be possible using standard SQL since in SQL statements there is no such concept of "previous record" or "next record" ... I think you will have to do the processing in the programming language that you are using to access the database.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24803114
if your database supports LEAD and LAG you can do this with sql.

I think there is a problem in your sample data...

2009/31/06   should be
2009/31/05  I think,  based on your expected results.

I've tested the query below in Oracle and it produces the expected results.
SELECT   DISTINCT
         CASE WHEN start_date - 1 <= prevend THEN prevstart ELSE start_date END start_date,
         CASE WHEN end_date + 1 >= nextstart THEN nextend ELSE end_date END end_date,
         VALUE
FROM     (SELECT start_date,
                 end_date,
                 VALUE,
                 LAG(start_date) OVER (PARTITION BY VALUE ORDER BY start_date) prevstart,
                 LAG(end_date) OVER (PARTITION BY VALUE ORDER BY start_date) prevend,
                 LEAD(start_date) OVER (PARTITION BY VALUE ORDER BY start_date) nextstart,
                 LEAD(end_date) OVER (PARTITION BY VALUE ORDER BY start_date) nextend
          FROM   your_table) x
ORDER BY start_date, end_date, VALUE

Open in new window

0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:WAeberhardt
ID: 24804249
sdstuber,

Regarding data, You are right. (mistake in my sample)

Can You give me the syntax of the LAG and LEAD scalar functions? I'll try to write them for my dialect.
Werner

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24804337
the syntax is in the code snippet

if your database doesn't support analytics you probably won't be able to write them

Instead you will probably need to do self-joins to find previous/next values
or iterate through the values with a procedural language.
0
 

Author Comment

by:WAeberhardt
ID: 24808124
Two benefits of answer

firstly, it was important to know, that there is no solution in standard SQL
secondly, the path to solve the problem in now clear (working with cursor. )
--> I can rebuild the solution in my dialect.

Werner
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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 …

722 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