Update column using counts of other columns??

[SimonBurrell]

Hi All

I need to calculate a Risk index that is the sum result of the following for each row in my table:

For each ROW...
If SSI_ASA > 2 THEN Count 1 ELSE Count 0
If Wound_Cl_ID> 2 THEN Count 1 ELSE Count 0
If Proc_Duration > P_75Percentile THEN Count 1 ELSE Count 0

The result column (SSI_Procedure_Risk) will be populated with either a 0,1,2,or 3 depending on the above count from the source columns.

E.g. Patient1 has SSI_ASA = 1,Wound_Cl_ID = 4 and Proc_Duration time longer than P_75Percentile therefore SSI_Procedure_Risk would be updated to 2.

Can I use local variables for the three source columns (I couldn't get this to work, but this is new to me!!) or is it best to just write an update query using multiple case functions?


Thanks heaps in advance.

Simon

 

[sguslan]

Use SQL case statement ...

I do not know how the Proc_Duration values are stored as Whole Integer or what ! So you may have to change the last part of the case statements.

select SSI_Procedure_Risk =
( case
when SSI_ASA > 2 THEN 1
else 0
end
)
+
( case
when Wound_Cl_ID> 2 then 1
else 0
end
)
+
( case
when Proc_Duration > 0.75 then 1
else 0
end
)

From PatientTable

 

[SimonBurrell]

Thanks Heaps for that. It didn't occur to me to add the case statements together.

Regards

Simon

 

[sguslan]

Glad it helped ! Happy SQLing!