Following is the scenario:
CREATE TABLE EMP_VACATION(
EMP_NO NUMBER(3),
VAC_TYPE NUMBER(1),
START_DATE DATE,
END_DATE DATE,
START_PERIOD_DATE DATE,
END_PERIOD_DATE DATE,
PRIMARY KEY (EMP_NO,VAC_TYPE,START_DATE))
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2001-01-02, 2001-01-20);
I need write procudre when insert any record for employee calculate START_PERIOD_DATE and END_PERIOD_DATE,
I have 4 case to caculate START_PERIOD_DATE and END_PERIOD_DATE for every emp_no :
------------------------------------------------------------------
The frist case::
When insert record first time for every emp_no calculate by this a way
START_PERIOD_DATE := 2001-01-02 -- START_DATE;
END_PERIOD_DATE := 2004-01-01;-- claculte by add 3 year on start_date
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2001-01-02 2001-01-20 2001-01-02 2004-01-01 -- first time
------------------------------------------------------------------
The second case::
if insert another record for same emp_no , I check for by start_date ,
Do you start_date within the previous last period for last vaction?
if ok then make same START_PERIOD_DATE and END_PERIOD_DATE for this record, like this:
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2002-05-01, 2002-05-15);
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2002-05-01 2002-05-15 2001-01-02 2004-01-01 -- same previous period
------------------------------------------------------------------
The third case::
if start_date without the previous last period for last vaction,like this:
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2006-06-01, 2006-06-20 );
in this status start_date without previous last period for last vaction,
START_PERIOD_DATE make next day for END_PERIOD_DATE for previous period for last vaction ,
and END_PERIOD_DATE make add 3 yearfrom START_PERIOD_DATE ,
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2006-06-01 2006-06-20 2004-01-02 2007-01-01 -- not same previous period,
------------------------------------------------------------------
The fourth case::
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2011-10-01, 2011-10-10 );
see start_date 2011-10-01 without last period(2004-01-02 , 2007-01-01)
and without next last period(2007-01-02, 2010-01-01),
start_date within next next last period( 2010-01-02, 2013-01-01 ),
and END_PERIOD_DATE make add 3 year from START_PERIOD_DATE
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2006-06-01 2006-06-20 2010-01-02 2013-01-01 -- not same previous period
------------------------------------------------------------------
*** note : The possibility start_date after 3 times or 5 or ... last period.
to become vaction for emp_id = 1 :::
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
-- first time
1 1 2001-01-02 2001-01-20 2001-01-02 2004-01-01 -- first time
1 1 2002-05-01 2002-05-15 2001-01-02 2004-01-01 -- same last period
1 1 2006-06-01 2006-06-20 2004-01-02 2007-01-01 -- add 3 year 1 time on last period
1 1 2006-06-01 2006-06-20 2010-01-02 2013-01-01 -- add 3 year 2 time on last period
1 1 2028-09-01 2028-09-05 2028-01-02 2031-01-01 -- add 3 year 4 time on last period
--How to calculate START_PERIOD_DATE and END_PERIOD_DATE for every time insert reocrd, like for 4 cases?
--I need to check start_date with last period date for same employee ,
And based upon calculate START_PERIOD_DATE and END_PERIOD_DATE,,,,,
Thanks a lot
CREATE TABLE EMP_VACATION(
EMP_NO NUMBER(3),
VAC_TYPE NUMBER(1),
START_DATE DATE,
END_DATE DATE,
START_PERIOD_DATE DATE,
END_PERIOD_DATE DATE,
PRIMARY KEY (EMP_NO,VAC_TYPE,START_DATE))
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2001-01-02, 2001-01-20);
I need write procudre when insert any record for employee calculate START_PERIOD_DATE and END_PERIOD_DATE,
I have 4 case to caculate START_PERIOD_DATE and END_PERIOD_DATE for every emp_no :
------------------------------------------------------------------
The frist case::
When insert record first time for every emp_no calculate by this a way
START_PERIOD_DATE := 2001-01-02 -- START_DATE;
END_PERIOD_DATE := 2004-01-01;-- claculte by add 3 year on start_date
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2001-01-02 2001-01-20 2001-01-02 2004-01-01 -- first time
------------------------------------------------------------------
The second case::
if insert another record for same emp_no , I check for by start_date ,
Do you start_date within the previous last period for last vaction?
if ok then make same START_PERIOD_DATE and END_PERIOD_DATE for this record, like this:
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2002-05-01, 2002-05-15);
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2002-05-01 2002-05-15 2001-01-02 2004-01-01 -- same previous period
------------------------------------------------------------------
The third case::
if start_date without the previous last period for last vaction,like this:
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2006-06-01, 2006-06-20 );
in this status start_date without previous last period for last vaction,
START_PERIOD_DATE make next day for END_PERIOD_DATE for previous period for last vaction ,
and END_PERIOD_DATE make add 3 yearfrom START_PERIOD_DATE ,
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2006-06-01 2006-06-20 2004-01-02 2007-01-01 -- not same previous period,
------------------------------------------------------------------
The fourth case::
INSERT VALUES EMP_VACATION(EMP_NO,VAC_TYPE,START_DATE,END_DATE)
VALUES(1, 1, 2011-10-01, 2011-10-10 );
see start_date 2011-10-01 without last period(2004-01-02 , 2007-01-01)
and without next last period(2007-01-02, 2010-01-01),
start_date within next next last period( 2010-01-02, 2013-01-01 ),
and END_PERIOD_DATE make add 3 year from START_PERIOD_DATE
TO become :
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
1 1 2006-06-01 2006-06-20 2010-01-02 2013-01-01 -- not same previous period
------------------------------------------------------------------
*** note : The possibility start_date after 3 times or 5 or ... last period.
to become vaction for emp_id = 1 :::
EMP_NO VAC_TYPE START_DATE END_DATE START_PERIOD_DATE END_PERIOD_DATE
-- first time
1 1 2001-01-02 2001-01-20 2001-01-02 2004-01-01 -- first time
1 1 2002-05-01 2002-05-15 2001-01-02 2004-01-01 -- same last period
1 1 2006-06-01 2006-06-20 2004-01-02 2007-01-01 -- add 3 year 1 time on last period
1 1 2006-06-01 2006-06-20 2010-01-02 2013-01-01 -- add 3 year 2 time on last period
1 1 2028-09-01 2028-09-05 2028-01-02 2031-01-01 -- add 3 year 4 time on last period
--How to calculate START_PERIOD_DATE and END_PERIOD_DATE for every time insert reocrd, like for 4 cases?
--I need to check start_date with last period date for same employee ,
And based upon calculate START_PERIOD_DATE and END_PERIOD_DATE,,,,,
Thanks a lot