Parent child count? [message #653620] |
Tue, 12 July 2016 13:53 |
|
P1Oracle
Messages: 60 Registered: August 2014 Location: Hyderabad
|
Member |
|
|
Hi Friends,
I have a scenario where i need to count Number of logged hours against tasks in projects
Where project contains Number of sub projects and Each Sub project contains n number of Tasks.i have tried like this but it is getting for only one project.
CREATE OR REPLACE FUNCTION im_project_total_bh_log_hours(integer,character)
RETURNS character varying AS
$BODY$
DECLARE
p_project_id alias for $1;
p_hours_type alias for $2;
v_name varchar;
v_nb_name varchar;
p_parent_id varchar;
BEGIN
SELECT PARENT_ID
into p_parent_id
FROM
IM_PROJECTS
WHERE
PROJECT_ID = p_project_id;
IF p_parent_id IS NULL THEN
SELECT
sum(hours),sum(non_billable_hours) into v_name,v_nb_name
FROM
im_hours
where
project_id in (SELECT PROJECT_ID FROM IM_PROJECTS WHERE PARENT_ID= p_project_id);
IF v_name IS NULL THEN
return 0.00;
ELSE
return v_name;
END IF;
ELSE
SELECT SUM(HOURS)
into v_name
from im_hours
where project_id= p_project_id;
return v_name;
END IF;
end;
|
|
|
|
|
|
|
|
|