Home » SQL & PL/SQL » SQL & PL/SQL » Retrieving a resource's working days in financial periods between hire and termination date (Oracle 9.2.0.4)
Retrieving a resource's working days in financial periods between hire and termination date [message #429567] Wed, 04 November 2009 09:41 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

I have a requirement to tabulate a resource's available days by period between their start date
and their termination date (if they have one)

Two tables will be involved:
BIZ_COM_PERIODS which has our financial periods identified with a start_date and end_date:

PERIOD START_DATE END_DATE
MAR09	21-FEB-09	20-MAR-09
APR09	21-MAR-09	24-APR-09
MAY09	25-APR-09	22-MAY-09
JUN09	23-MAY-09	19-JUN-09
JUL09	20-JUN-09	24-JUL-09
AUG09	25-JUL-09	21-AUG-09
SEP09	22-AUG-09	18-SEP-09
OCT09	19-SEP-09	23-OCT-09
NOV09	24-OCT-09	20-NOV-09
DEC09	21-NOV-09	18-DEC-09





and a resources table:
SRM_RESOURCES
RESCODE HIRE_DATE       DATE_OF_TERMINATION
69172	14-AUG-06	01-NOV-09
66180	01-MAY-06	
73975	01-JUL-07	
69174	28-AUG-06	
73690	01-JUN-07	29-AUG-09
70804	01-NOV-06	10-SEP-09
66074	01-OCT-07	
77824	19-AUG-09	
76153	06-MAY-08	
60615t	01-JAN-06	
60317T	02-FEB-07	
C02757	13-JAN-09	
60668TH	22-FEB-09	10-APR-09
66201UK	01-MAY-07	
60365T	18-FEB-08	
60192t	01-JAN-06	
60295T	23-JAN-06	
60511T	03-FEB-06





I need to group, by resource and period name, how many network days they have in each period.
if there is a null in date_of_termination they are still active.

The query will need to see how many working days there are in each period in the date range, excluding any time between the start and their date of hire and the end and their date of termination.


how can I achieve this?

help is appreciated.
thanks in advance,
Matt


Re: Retrieving a resource's working days in financial periods between hire and termination date [message #429570 is a reply to message #429567] Wed, 04 November 2009 10:24 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should know now:
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Previous Topic: Need help converting Access SQL to Oracle
Next Topic: Autonomous Transaction in a Trigger
Goto Forum:
  


Current Time: Wed Dec 07 23:58:53 CST 2016

Total time taken to generate the page: 0.10018 seconds