Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2012
09:35 AM

06-05-2012
09:35 AM

Data In Table

COL1 COL2 Start End

A B 1 5

A C 3 15 -- Split into two records

A D 7 8

A E 16 100

Result- Required

COL1 COL2 Start End

A B 1 2

A C 3 6

A D 7 8

A C 9 15

A E 9 15

A B 16 100

Thanks

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2012
11:27 AM

06-05-2012
11:27 AM

I guess you need to explain in more detail what you want to achive.

What is the rule for the split an changes in col2?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2012
08:48 PM

06-05-2012
08:48 PM

OK. Here is the situation.

The data is regarding the effectivity of col2 for Col1 for a eff_st and eff_end date range.

I have just used integers in place of dates for simplicity.

I have shown the data below in the grid format to get a clearer picture.

A-B has to split to create two records once before the effectivity of A-C and one after the effectivity of A-C.

Hope that explains. The data is coming like that due to some bad data in the source system.

Data | ||||||||

A | B | 1 | 7 | |||||

A | C | 3 | 5 | |||||

Result | ||||||||

A | B | 1 | 2 | |||||

A | C | 3 | 5 | |||||

A | B | 6 | 7 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2012
02:24 PM

06-06-2012
02:24 PM

You will have to modify this considerable as per your records and use cases but in essence something like below should work

/** Insert which will take care of your third row **/

INSERT INTO TMP1

SEL A.COL1,A.COL2,B.END1+1,A.END1,'n'

FROM

TMP1 A

JOIN TMP1 B

ON A.COL1=B.COL1

AND A.STRT <B.STRT

AND A.END1>B.END1

;

/** Update which will modify your first row **/

UPDATE TMP1

FROM

(

SEL A.COL1,A.COL2,A.STRT,B.STRT-1 END1

FROM TMP1 a ,TMP1 B

WHERE

A.COL1 =B.COL1

AND

A.COL2<>B.COL2

AND

B.STRT BETWEEN A.STRT AND A.END1

) X

SET END1=X.END1

WHERE

X.COL1=TMP1.COL1

AND

X.COL2=TMP1.COL2

AND

X.STRT=TMP1.STRT

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-09-2012
07:20 AM

06-09-2012
07:20 AM

Are you on TD13.10?

Then the new TD_SEQUENCED_? functions for periods might help, too.

Dieter

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.