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

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
Asked:
ACSDBA
  • 5
  • 3
  • 2
  • +1
1 Solution
 
paquicubaCommented:
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
 
actonwangCommented:
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
 
actonwangCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
actonwangCommented:
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
 
actonwangCommented:
i think paq got it first :)
0
 
sathyagiriCommented:
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
 
paquicubaCommented:
You're right buddy:

APPROVED-TOTAL_RETURNED+LEAD(ELIGIBLE_TO_RETURN) OVER ( ORDER BY OCC_PHASE ) NEXT_PHASE
0
 
actonwangCommented:
all credits to paq. idea was gotten by paq first.
0
 
ACSDBAAuthor Commented:
Just for my education.. what does the lead and over statements do? I've never seen that used before.
0
 
sathyagiriCommented:
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
0
 
paquicubaCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now