postgresql7.2
SELECT
p.item_id,
p.project_id,
repeat(:indent_pattern, (tree_level(p.tree_sortkey) - 5)* :indent_factor) as indent,
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') as planned_start_date,
to_char(p.planned_end_date, 'YYYY MM DD') as planned_end_date,
p.ongoing_p,
p.estimated_completion_date - p.planned_end_date as days_late,
p.earliest_completion_date - p.planned_end_date as days_late_earliest,
p.latest_completion_date - p.planned_end_date as days_late_latest,
actual_hours_completed,
estimated_hours_total
FROM pm_projectsx p, cr_items i
WHERE p.project_id = i.live_revision
ORDER BY i.tree_sortkey