How to calculate period date

[oraprog]

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

 

[Dagon]

What on Earth does mean?

Do you start_date within the previous last period for last vaction?



For Oracle-related work, contact me through Linked-In.

 

[oraprog]

I mean, check start_date between START_PERIOD_DATE and END_PERIOD_DATE

 

[Dagon]

Yasser,

I think there are limits to which I and other people on this site are willing to do your job for you. We've given you a lot of help already but I think just giving us the requirements of your latest project and expecting us to deliver a solution is going a bit too far. I suggest you try to provide a solution yourself and, if there are specfic problems with parts of it that you encounter, we may be able to help out.



For Oracle-related work, contact me through Linked-In.

 

[oraprog]

Thanks Dagon for replay