Conditionally calculation

  • 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
 
You must use a join statement between the two tables 1 and 2:

Select Table2.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


Because you have two Territory_Group values in the two table 1 and 2, you
must write either Table1 or Table2 before the field Territory_Group in the
Select statement to tell SQL-Server which one of these two you want to
retrieve. You must do this even if the values for Territory_Group are the
same in both tables.
 
Thank you very much for your response. But I also need the condition of
"AOI_Low<COV_A<=AOI_High". How can I do this?
Thanks,
Lily
 
Oups, sorry, I forgot about that. I'm not sure to really understand what
this condition mean but probably that all you have to do is to add a WHERE
condition:

Select Table2.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

Where (AOI_Low < COV_A) And (COV_A <= AOI_High)
 
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
 
Probably because the UNION is removing any duplicate from both sets.

Use UNION ALL instead of UNION and make sure that having only 100 records
instead of 200 is not a better solution.
 
Back
Top