Selecting transactions as one row
I have a requirement when I need to generate a report on current software projects. Two of the columns are the date of the last milestone and the date of the previous milestone. Obviously, milestones are stored in a transaction table, so we can have many milestones for each project.
I got here so far, but now I'm having problems:
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 is that not all projects will have a final milestone, and not all projects will have more than one milestone. I've tried left joins, but then returning multiple lines for each project (which I should avoid).
I am using Oracle 10, so if there is something I can use in PL / SQL I will do that too.
+2
source to share
1 answer
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 turned JOIN into LEFT JOIN if the project doesn't have a milestone.
+4
source to share