Conditionally calculation

[Lily]

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

 

[Sylvain Lafontaine]

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.

 

[Lily]

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

 

[Sylvain Lafontaine]

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)

 

[Lily]

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

 

[Sylvain Lafontaine]

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.