Bitemporal table

Database
Enthusiast

Bitemporal table

Hi,

 

I have a temporal table as below; 

CREATE MULTISET TABLE JOB ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID BIGINT NOT NULL,
JOB_CD VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

HOURS_PER_WEEK INTEGER,
EMP_ID BIGINT,
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.

 

Example:

Current - 2017-07-18
ID JOB_CDHOURS_PER_WEEKEMP_IDVLD_DTSYS_VLD_DT
1abc40123(2017-07-18, 9999-12-31)(2017-07-18, 9999-12-31)
2xyz40234(2017-07-18, 9999-12-31)(2017-07-18, 9999-12-31)
3def40345(2017-07-18, 9999-12-31)(2017-07-18, 9999-12-31)

 

New - 2017-07-20
ID JOB_CDHOURS_PER_WEEKEMP_ID
1abc35123

 

Want it to be like 
ID JOB_CDHOURS_PER_WEEKEMP_IDVLD_DTSYS_VLD_DT
1abc40123(2017-07-18, 2017-07-19)(2017-07-19, 2017-07-20)
2xyz40234(2017-07-18, 9999-12-31)(2017-07-19, 9999-12-31)
3def40345(2017-07-18, 9999-12-31)(2017-07-19, 9999-12-31)
1abc35123(2017-07-20, 9999-12-31)(2017-07-20, 9999-12-31)

 

Thanks in advance.