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
] 
Members
Search
Help
Register
Login
Home
