Conditional Calculation (SQL)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would one go about conditional calculation based on other fields? FOr
example
Table 1: Territory_Group, AOI_High, AOI_Low, Factor_High, Factor_Low
Table 2: Territory_Group, Cov_A
I need a statement to give a data set with the following fields:
Territory_Group, Cov_A,
((AOI_High-Cov_A)*Factor_low+(COv_A-AOI_Low)*Factor_High)/(AOI_High-AOI_Low)
as AOI_Factor
Given that Table1. Territory_Group=Table2. Territory_Group AND
(AOI_Low<COV_A<=AOI_High)

Thanks a million!
Lily
 
Lily said:
How would one go about conditional calculation based on other fields? FOr
example
Table 1: Territory_Group, AOI_High, AOI_Low, Factor_High, Factor_Low
Table 2: Territory_Group, Cov_A
I need a statement to give a data set with the following fields:
Territory_Group, Cov_A,
((AOI_High-Cov_A)*Factor_low+(COv_A-AOI_Low)*Factor_High)/(AOI_High-AOI_Low)
as AOI_Factor
Given that Table1. Territory_Group=Table2. Territory_Group AND
(AOI_Low<COV_A<=AOI_High)
Click to expand...


The calculation of the expression should be pretty much the
way you wrote it. The key is to get all the needed fields
from the re;ated records in both tables by specifying the
query's Join clause. The query's SQL statment wil be
something like:

SELECT Table1.Territory_Group, Cov_A,
((AOI_High - Cov_A) * Factor_low +
(COv_A - AOI_Low) * Factor_High) /
(AOI_High - AOI_Low) As AOI_Factor
FROM Table1 INNER JOIN Table2
ON Table1.Territory_Group=Table2.Territory_Group
AND AOI_Low < COV_A And COV_A <= AOI_High

This kind of ON clause can not be specified in the query
design grid so make all further changes in SQL view.
 
Thanks for your help. It works!
Now my problem becomes:
if Cov_A <750 then .............
if Cov_A > 750 then............
I use a union operator to unite both sql

proc sql;
create table Ten_risks_3 as
Select *,
((AOI_High-Cov_A)*H3_low+(COv_A-AOI_Low)*H3_High)/(AOI_High-AOI_Low) as
H3AOI_Factor
from Ten_risks_2 a inner join IPM_MAN.M_AOI b
on a.TerrGrp=b.TerritoryGrp and AOI_Low<=cov_A<AOI_High
Where COv_A <750000
Union
Select *,6.432+.0086*(Cov_A-750000)/1000 as H3AOI_Factor
from Ten_Risks_2 a inner join IPM_MAN.M_AOI b
on a.TerrGrp=b.TerritoryGrp
where Cov_A>=750000;
Quit;

The problem is that I should have get 200 records w/o union, but only got
100 records after union. What is going on here? How can I do it?

THanks,
lily
 
Try using UNION ALL instead of UNION and see if that gives you the desired
results. UNION returns DISTINCT records.
 
Back
Top