Selecting transactions as a single row

Question

Hey all,

I have a requirement where I need to generate a report about current software projects. Two of the columns are the date of the latest milestone and the date of the milestone previous to that. Obviously, milestones are stored in a transaction table so we can have many milestone per project.

I've gotten to here so far, but now I'm having issues:

select  proj.*
from zsof_projects proj
join zsof_milestones current_milestone on current_milestone.product_id = proj.id
join zsof_milestones last_milestone on last_milestone.product_id = proj.id
join    (
        select product_id, max(actual_date) maxDate
        from zsof_milestones
        group by product_id
        ) a on a.product_id = current_milestone.product_id and a.maxDate = current_milestone.actual_date
join    (
        select mile.product_id, max(actual_date) maxDate
        from zsof_milestones mile
        join    (
                select product_id, max(actual_date) maxDate
                from zsof_milestones
                group by product_id
                ) a on a.product_id = mile.product_id and mile.actual_date < a.maxDate
        group by mile.product_id
        ) b on b.product_id = last_milestone.product_id and b.maxDate = last_milestone.actual_date
order by proj.id;

The problem I have is that not all projects will have a latest milestone and not all projects will have more than one milestone. I've tried left joins but then I get back multiple rows per project (which is something I need to avoid).

I'm using Oracle 10, so if there's something I can use in PL/SQL I'll take that as well.

Thanks in advance, Matt

Answer 1

Hi Matthew,

use analytics :)

SELECT v.*
  FROM (SELECT proj.*, actual_date, 
               MAX(actual_date) over(PARTITION BY ms.product_id) last_milestone,
               lag(actual_date) over(PARTITION BY ms.product_id 
                                     ORDER BY actual_date) previous_milestone
           FROM zsof_projects proj
           LEFT JOIN zsof_milestones ms ON ms.product_id = proj.id) v
 WHERE last_milestone = actual_date
    OR (actual_date IS NULL AND last_milestone IS NULL)

Update: I transformed the JOIN into a LEFT JOIN in case a project doesn't have a milestone.