Home » SQL & PL/SQL » SQL & PL/SQL » How to use function in this case?? (9.2.0.6)
How to use function in this case?? [message #348528] Wed, 17 September 2008 02:32 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
I need to find the total base hours for a given period. Following are the DDL and DMLs
Create table:
CREATE TABLE EMP_BASE_HOURS(PERSON_ID NUMBER, FROM_DATE DATE, TO_DATE DATE, BASE_HRS NUMBER);


Insert Statements:
INSERT INTO EMP_BASE_HOURS VALUES(100, TO_DATE('14-JUL-2008', 'DD-MON-RRRR'), TO_DATE('02-AUG-2008', 'DD-MON-RRRR'), 9);
INSERT INTO EMP_BASE_HOURS VALUES(100, TO_DATE('03-AUG-2008', 'DD-MON-RRRR'), TO_DATE('31-DEC-4712', 'DD-MON-RRRR'), 8);
INSERT INTO EMP_BASE_HOURS VALUES(101, TO_DATE('01-JUL-2008', 'DD-MON-RRRR'), TO_DATE('05-AUG-2008', 'DD-MON-RRRR'), 9);
INSERT INTO EMP_BASE_HOURS VALUES(101, TO_DATE('06-AUG-2008', 'DD-MON-RRRR'), TO_DATE('15-AUG-2008', 'DD-MON-RRRR'), 6);
INSERT INTO EMP_BASE_HOURS VALUES(101, TO_DATE('16-AUG-2008', 'DD-MON-RRRR'), TO_DATE('31-DEC-4712', 'DD-MON-RRRR'), 8);
INSERT INTO EMP_BASE_HOURS VALUES(200, TO_DATE('01-JUL-2008', 'DD-MON-RRRR'), TO_DATE('31-AUG-2008', 'DD-MON-RRRR'), 6);
INSERT INTO EMP_BASE_HOURS VALUES(200, TO_DATE('01-SEP-2008', 'DD-MON-RRRR'), TO_DATE('31-DEC-4712', 'DD-MON-RRRR'), 9);



SELECT PERSON_ID, TO_CHAR(FROM_DATE, 'DD-MON-YYYY') AS FROM_DATE, TO_CHAR(TO_DATE, 'DD-MON-YYYY') AS TO_DATE, BASE_HRS FROM EMP_BASE_HOURS ORDER BY PERSON_ID, TO_DATE(FROM_DATE, 'DD-MON-YYYY');

PERSON_ID	FROM_DATE	 TO_DATE	BASE_HRS
100		14-JUL-2008	 02-AUG-2008	 9
100		03-AUG-2008	 31-DEC-4712	 8
101		01-JUL-2008	 05-AUG-2008	 9
101		06-AUG-2008	 15-AUG-2008	 6
101		16-AUG-2008	 31-DEC-4712	 8
200		01-JUL-2008	 31-AUG-2008	 6
200		01-SEP-2008	 31-DEC-4712	 9


I want to create a function wherein i'll input the person_id, from_date and to_date as parameters and it should return the total number of base hours for that particular period.

Example:
For Person_ID = 100
From_Date = 01-Aug-2008
To_Date = 31-Aug-2008
Total_BaseHrs = 250 hrs

250 hours is arrived like this:
01-Aug-2008 to 02-Aug-2008(2 days) base hrs is 9hrs  =  2*9 =  18
03-Aug-2008 to 31-Aug-2008(29 days) base hrs is 8 hrs= 29*8 = 232
Total Base Hours = 250

For Person_ID = 101
From_Date = 01-Aug-2008
To_Date = 31-Aug-2008
Total BaseHrs=233

Calculation:
01-Aug-2008 to 05-Aug-2008(5 days) base hrs is 9 hrs =  5*9=45
06-Aug-2008 to 15-Aug-2008(10 days)base hrs is 6 hrs = 10*6=60
16-Aug-2008 to 31-Aug-2008(16 days)base hrs is 8 hrs = 16*8=128
Total Base hours = 233


Can anyone help me how to accomplish this? I have tried hard, but with no luck.

Thanks in advance
Sandi
Re: How to use function in this case?? [message #348536 is a reply to message #348528] Wed, 17 September 2008 02:59 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One possible solution is a loop per day from-to in the function and then sum up the hours for the single days.

SQL> CREATE OR REPLACE FUNCTION get_hours
  2   (v_emp NUMBER, v_from DATE, v_to DATE )
  3    return NUMBER
  4  IS
  5      v_tmp_hours NUMBER;
  6      v_ret_hours NUMBER;
  7      v_counter  DATE;
  8  BEGIN
  9
 10      v_ret_hours := 0;
 11
 12      -- Loop per day from-to
 13      FOR v_counter IN 0 .. (v_to -  v_from) LOOP
 14
 15         -- Select and add hours for that day
 16         SELECT Nvl(Sum(BASE_HRS),0) 
 17           into v_tmp_hours
 18           FROM EMP_BASE_HOURS
 19          WHERE v_from +  v_counter BETWEEN FROM_DATE AND TO_DATE
 20            AND PERSON_ID         =  v_emp;
 21
 22         v_ret_hours := v_ret_hours + v_tmp_hours;
 23
 24      END LOOP;
 25
 26      RETURN v_ret_hours;
 27  END;
 28  /

Function created.

SQL>
SQL> SELECT get_hours(100,
  2                   TO_DATE('01-AUG-2008', 'DD-MON-RRRR'),
  3                   TO_DATE('31-AUG-2008', 'DD-MON-RRRR')) hours
  4    FROM dual;

     HOURS
----------
       250

SQL>
SQL>
SQL>


But I have the feeling someone will come up with a nifty SQL-Only solution sooner or later, too. ;-P

Edit: Added sum/nvl stuff in case of possible overlaps or empty days.

[Updated on: Wed, 17 September 2008 03:02]

Report message to a moderator

Re: How to use function in this case?? [message #348549 is a reply to message #348528] Wed, 17 September 2008 03:44 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
With only SQL.

Processing ...
SELECT :inp_person_id,:inp_from_date,:inp_to_date,
	sum((1+least(:inp_to_date,to_date)-greatest(:inp_from_date,from_date))*base_hrs) as total
FROM EMP_BASE_HOURS a
where to_date > :inp_from_date
	and from_date < :inp_to_date
	and  person_id = :inp_person_id
Query finished, retrieving results...
:INP_PERSON_ID :INP_FROM_DATE  :INP_TO_DATE  TOTAL     
-------------- -------------- -------------- ------
100            1/08/2008      31/08/2008     250 

1 row(s) retrieved



Bye Alessandro
Re: How to use function in this case?? [message #348558 is a reply to message #348528] Wed, 17 September 2008 04:30 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thank you. Both the approach is great for me.

Regards,
Sandi
Re: How to use function in this case?? [message #348559 is a reply to message #348549] Wed, 17 September 2008 04:32 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi Alessandro,
Using your approach, can we get rid of saturdays and sundays before multiplying with base hours?

Thank you again.

Regards,
Sandi
Re: How to use function in this case?? [message #348562 is a reply to message #348559] Wed, 17 September 2008 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Brute force:
SQL> def from_date=to_date('01-Aug-2008','dd-Mon-YYYY')
SQL> def to_date=to_date('31-Aug-2008','dd-Mon-YYYY')
SQL> def Person_ID=100
SQL> with 
  2    calendar as 
  3      ( select &from_date+level-1 day from dual connect by level <= &to_date-&from_date+1 )
  4  select sum(base_hrs*count(*)) total_hours
  5  from emp_base_hours, calendar
  6  where person_id = &person_id
  7    and day between from_date and to_date
  8    and trim(to_char(day,'Day')) not in ('Sunday','Saturday')
  9  group by from_date, base_hrs
 10  /

TOTAL_HOURS
-----------
        169

1 row selected.

Regards
Michel
Re: How to use function in this case?? [message #348635 is a reply to message #348559] Wed, 17 September 2008 08:04 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Quote:

saturdays and sundays before multiplying with base hours



Michel's solution works fine but it uses a table to store days in that period and it may be quite expensive in terms of resources compared to what we're trying to evaluate.

To do it numerically you should decompose the total in three parts.

1) hours of the period between the first day and the next friday
2) hours of the period between the next monday and the last monday
3) hours of the period between the last monday and the last day


And this is the result
Processing ...
with periods as (
		select :inp_from_date as inp_from_date,:inp_to_date as inp_to_date,:inp_person_id as inp_person_id,
			base_hrs,from_date,to_date,
			greatest(:inp_from_date,from_date)as per_begin,
			least(:inp_to_date,to_date) as per_end
		FROM EMP_BASE_HOURS a
		where to_date > :inp_from_date
			and from_date < :inp_to_date
			and  person_id = :inp_person_id
	), filtered_periods as (
		select inp_from_date,inp_to_date,inp_person_id,
			base_hrs,from_date,to_date,
			per_begin,
			trunc(per_begin,'dy')+4 as first_end,
			trunc(per_begin,'dy')+7 as mid_begin,
			trunc(per_end,'dy') as mid_end,
			least(trunc(per_end,'dy')+4,per_end) as per_end
		from periods
	), working_days as (
		select inp_from_date,inp_to_date,inp_person_id,
			base_hrs,from_date,to_date,
			per_begin,first_end,mid_begin,mid_end,per_end,
			case 
				when per_begin <= first_end then
					first_end - per_begin + 1
				else 
					0
			end + case
				when mid_begin < mid_end then
					(((mid_end - mid_begin) / 7)*5)
				else
					0
				end + case 
				when ( mid_begin < mid_end and mid_end <= per_end ) then
					per_end - mid_end + 1
				else
					0
			end as days
		from filtered_periods
	)
select inp_from_date,inp_to_date,inp_person_id,sum(days*base_hrs) as total_hrs
from working_days
Query finished, retrieving results...
INP_FROM_DATE INP_TO_DATE INP_PERSON_ID TOTAL_HRS    
------------- ----------- ------------- ---------
01/08/2008    31/08/2008  100           169 

1 row(s) retrieved



Bye Alessandro

[Updated on: Wed, 17 September 2008 08:37]

Report message to a moderator

Previous Topic: Help
Next Topic: displaying table definition
Goto Forum:
  


Current Time: Sat Dec 10 14:43:55 CST 2016

Total time taken to generate the page: 0.07976 seconds