• Status: Solved
• Priority: Medium
• Security: Public
• Views: 380

# 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!
0
ACSDBA
• 5
• 3
• 2
• +1
1 Solution

Commented:
SELECT OCC_PHASE, ELIGIBLE_TO_RETURN, APPROVED, TOTAL_RETURNED,
APPROVED+TOTAL_RETURNED+LEAD(ELIGIBLE_TO_RETURN) OVER ( ORDER BY OCC_PHASE ) NEXT_PHASE
FROM(
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)
0

Commented:
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)
/

0

Commented:
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)
/
0

Commented:
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
0

Commented:
i think paq got it first :)
0

Commented:
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
0

Commented:
You're right buddy:

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

Commented:
all credits to paq. idea was gotten by paq first.
0

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

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

Refer to this
0

Commented:
Thanks to all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.