Solved

Data Calculation of multiple rows

Posted on 2006-07-12
11
365 Views
Last Modified: 2012-06-21
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
Comment
Question by:ACSDBA
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 17090953
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17090996
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17091006
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17091014
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17091051
i think paq got it first :)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:sathyagiri
ID: 17091061
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 17091070
You're right buddy:

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

Expert Comment

by:actonwang
ID: 17091149
all credits to paq. idea was gotten by paq first.
0
 

Author Comment

by:ACSDBA
ID: 17091211
Just for my education.. what does the lead and over statements do? I've never seen that used before.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17091243
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 17091899
Thanks to all!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now