Solved

Data Calculation of multiple rows

Posted on 2006-07-12
11
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle encryption 12 73
pivot rows to columns 1 60
sum of columns in a row in oracle 3 62
error starting form builder in 11g 2 49
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

752 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