asked on

Data Calculation of multiple rows

select cph.reoccupancy_phase,
itr.approved,
itr.public_housing+itr.tax_credit+itr.pbra+itr.market total_returned,
itr.next_phase
from pmco.communities com,
pmco.community_occ_phase cph,
(select m.hoh_project_no, count(m.hoh_ss_number) total_households
from pmco.hoh_master m
group by m.hoh_project_no) mas,
pmco.itr_eligible_to_return_v itr
where com.affected_rel_community = 'Yes'
and com.community_project_number = :PROJECT_NO
and com.community_project_number = cph.community_id
and com.community_project_number = mas.hoh_project_no
and com.community_project_number = itr.project_no
and itr.return_phase = cph.reoccupancy_phase
order by 1

occ_phase|eligible_to_return|approved|total_returned|next_phase
----------------------------------------------------------------------------------------------
1 48 45 44
2 22 0 0
3 1 1 0
4 1 0 0

Experts,
I have the following query and the output from that query that I need to ask a question about. I want to calculate the last column(next phase). The logic for that calculation is this. Ex.. 45(from occ_phase1) - 44(also from occ_phase1) + 22(from occ_phase2) to calculate the next phase for occ_phase 1.

How in the world do I write that? I am totally stumped at this point. Thanks!

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Acton Wang

use the following to do it:

select occ_phase, ..... , approved - total_returned- lead(eligible_to_return) over (order by occ_phase) next_phase
from
(select occ_phase, eligible_to_return, approved, total_returned from XXXX)
/

Acton Wang

should be:

select occ_phase, ..... , approved - total_returned+ lead(eligible_to_return) over (order by occ_phase) next_phase
from
(select occ_phase, eligible_to_return, approved, total_returned from XXXX)
/

Acton Wang

your original query doesn't match the output you listed so I put a dummy subquery there but you should get the idea how to do it.

acton

Acton Wang

i think paq got it first :)

sathyagiri

select cph.reoccupancy_phase,
itr.approved,itr.public_housing+itr.tax_credit+itr.pbra+itr.market total_returned,
lead(itr.eligible_to_return,1) over (order by cph.reoccupancy_phase) + itr.approved - (itr.public_housing+itr.tax_credit+itr.pbra+itr.market)
from pmco.communities com,
pmco.community_occ_phase cph,(select m.hoh_project_no, count(m.hoh_ss_number) total_households
from pmco.hoh_master m group by m.hoh_project_no) mas,
pmco.itr_eligible_to_return_v itr
where com.affected_rel_community = 'Yes'
and com.community_project_number = :PROJECT_NO
and com.community_project_number = cph.community_id
and com.community_project_number = mas.hoh_project_no
and com.community_project_number = itr.project_no
and itr.return_phase = cph.reoccupancy_phase
order by 1

paquicuba

You're right buddy:

APPROVED-TOTAL_RETURNED+LEAD(ELIGIBLE_TO_RETURN) OVER ( ORDER BY OCC_PHASE ) NEXT_PHASE

Acton Wang

all credits to paq. idea was gotten by paq first.

ACSDBA

ASKER

Just for my education.. what does the lead and over statements do? I've never seen that used before.

sathyagiri

Lead is an analytical function that allows you to look at the value of next row without a self join.

Refer to this
http://www.akadia.com/services/ora_analytic_functions.html#LEAD

paquicuba

Thanks to all!