Need help with some handy code for this situation

Database
Enthusiast

Need help with some handy code for this situation

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

Re: Need help with some handy code for this situation

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?

Enthusiast

Re: Need help with some handy code for this situation

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
Enthusiast

Re: Need help with some handy code for this situation

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

;

Junior Contributor

Re: Need help with some handy code for this situation

Are you on TD13.10?

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

Dieter