All Forums Database
mjasrotia 66 posts Joined 08/11
05 Jun 2012
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

ulrich 816 posts Joined 09/09
05 Jun 2012

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?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

mjasrotia 66 posts Joined 08/11
05 Jun 2012

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

 

Jigar 70 posts Joined 09/11
06 Jun 2012

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

;

 

dnoeth 4628 posts Joined 11/04
09 Jun 2012

Are you on TD13.10?
Then the new TD_SEQUENCED_? functions for periods might help, too.

Dieter

Dieter

You must sign in to leave a comment.