postgresql7.3
SELECT
CASE
WHEN SUM(rx.estimated_hours_work) = 0
THEN
100
WHEN SUM(rx.estimated_hours_work) IS NULL
THEN
0
ELSE
FLOOR(SUM(rx.percent_complete * rx.estimated_hours_work) /
SUM(rx.estimated_hours_work))
END AS percent_complete,
p.item_id as project_item_id,
p.project_id,
p.status_id,
p.parent_id as folder_id,
p.object_type as content_type,
p.title as project_name,
p.project_code,
to_char(p.planned_start_date, 'YYYY-MM-DD HH24:MI:SS') as planned_start_date,
to_char(p.planned_end_date, 'YYYY-MM-DD HH24:MI:SS') as planned_end_date,
p.ongoing_p,
c.category_id,
c.category_name,
p.earliest_finish_date - current_date as days_to_earliest_finish,
p.latest_finish_date - current_date as days_to_latest_finish,
p.actual_hours_completed,
p.estimated_hours_total,
to_char(p.estimated_finish_date, 'YYYY-MM-DD HH24:MI:SS') as estimated_finish_date,
to_char(p.earliest_finish_date, 'YYYY-MM-DD HH24:MI:SS') as earliest_finish_date,
to_char(p.latest_finish_date, 'YYYY-MM-DD HH24:MI:SS') as latest_finish_date,
case when o.name is null then '--no customer--' else o.name
end as customer_name,
o.organization_id as customer_id,
f.package_id,
to_char(p.creation_date, 'YYYY-MM-DD HH24:MI:SS') as creation_date,
to_char(p.planned_start_date, 'YYYY-MM-DD HH24:MI:SS') as start_date
FROM pm_projectsx p
LEFT JOIN pm_tasks_revisionsx rx ON rx.parent_id =
p.item_id
LEFT JOIN pm_project_assignment pa
ON p.item_id = pa.project_id
LEFT JOIN organizations o ON p.customer_id =
o.organization_id
LEFT JOIN (
select
om.category_id,
om.object_id,
t.name as category_name
from
category_object_map om,
category_translations t,
categories ctg
where
om.category_id = t.category_id and
ctg.category_id = t.category_id and
ctg.deprecated_p = 'f')
c ON p.item_id = c.object_id,
cr_items i,
cr_items i2,
cr_folders f,
pm_roles pr
WHERE
p.project_id = i.live_revision
and rx.object_id = i2.live_revision
and i.parent_id = f.folder_id
and f.package_id in ($package_ids)
and exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = p.project_id
and ppm.privilege = 'read'
and ppm.party_id = :user_id)
[template::list::filter_where_clauses -and -name projects]
GROUP BY
p.item_id,
p.project_id,
p.status_id,
p.parent_id,
p.object_type,
p.title,
p.project_code,
p.planned_start_date,
p.planned_end_date,
p.ongoing_p,
c.category_id,
c.category_name,
p.earliest_finish_date,
p.latest_finish_date,
p.actual_hours_completed,
p.estimated_hours_total,
p.estimated_finish_date,
o.name,
o.organization_id,
f.package_id,
p.creation_date
[template::list::orderby_clause -orderby -name projects]