Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to calculate period date

Status
Not open for further replies.

oraprog

Programmer
Aug 8, 2011
11
SA
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
 
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.
 
I mean, check start_date between START_PERIOD_DATE and END_PERIOD_DATE
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top