I have a temporal table as below;
CREATE MULTISET TABLE JOB ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
ID BIGINT NOT NULL,
JOB_CD VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
VLD_DT PERIOD(DATE) NOT NULL AS VALIDTIME,
SYS_VLD_DT PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME
SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME PRIMARY KEY ( ID ),
PARTITION BY ( CASE_N(
(END(VLD_DT )) IS UNTIL_CHANGED,
(END(VLD_DT )) IS NOT UNTIL_CHANGED),CASE_N(
(END(SYS_VLD_DT )) IS UNTIL_CLOSED,
(END(SYS_VLD_DT )) IS NOT UNTIL_CLOSED) );
which tracks which Job code with the hours per week assigned to an employee at that point of time.
Currently I am doing sequnced validtime delete to close the record and nonsequenced validtime insert to update the emp with any change in the hours per week.
When I am doing that my old record is getting logically deleted in transactiontime but it still show open in validtime.
But, I want to close the old record's vld_to_date with new record's vld_from_dt -1.
|Current - 2017-07-18|
|1||abc||40||123||(2017-07-18, 9999-12-31)||(2017-07-18, 9999-12-31)|
|2||xyz||40||234||(2017-07-18, 9999-12-31)||(2017-07-18, 9999-12-31)|
|3||def||40||345||(2017-07-18, 9999-12-31)||(2017-07-18, 9999-12-31)|
|New - 2017-07-20|
|Want it to be like|
|1||abc||40||123||(2017-07-18, 2017-07-19)||(2017-07-19, 2017-07-20)|
|2||xyz||40||234||(2017-07-18, 9999-12-31)||(2017-07-19, 9999-12-31)|
|3||def||40||345||(2017-07-18, 9999-12-31)||(2017-07-19, 9999-12-31)|
|1||abc||35||123||(2017-07-20, 9999-12-31)||(2017-07-20, 9999-12-31)|
Thanks in advance.