Predicated Score query block

Just my opinion.. ignore me if you want but we've had this happen in a production environment with another stored proc.

The predicated score block at https://github.com/i2b2plugins/loyalty_cohort/blob/8bbd4d2e542db4040806fb5a357014cf19f6f996/usp_LoyaltyCohort_v6.sql#L453

is one accidental find-replace away from annihilation. I suggest a 2nd table in addition to the path table your asking people to maintain:

DROP TABLE IF EXISTS XREF_LoyaltyCode_PSCoeff
GO

CREATE TABLE XREF_LoyaltyCode_PSCoeff (
  FIELD_NAME VARCHAR(50),
  COEFF NUMERIC(4,3)
)

INSERT INTO XREF_LoyaltyCode_PSCoeff (FIELD_NAME, COEFF)
VALUES ('MDVisit_pname2',0.049)
,('MDVisit_pname3',0.087)
,('MedicalExam',0.078)
,('Mammography',0.075)
,('PapTest',0.009)
,('PSATest',0.103)
,('Colonoscopy',0.064)
,('FecalOccultTest',0.034)
,('FluShot',0.102)
,('PneumococcalVaccine',0.031)
,('BMI',0.017)
,('A1C',0.018)
,('meduse1',0.002)
,('meduse2',0.074)
,('INP1_OPT1_Visit',0.091)
,('OPT2_Visit',0.050)
,('Num_Dx1',-0.026)
,('NUM_DX2',0.037)
,('ED_Visit',0.078)
,('Routine_Care_2',0.049);

Once this table is created, you can maintain the coefficients easily in one place, and know that they're safe. The PS can be calculated for the set with the following unpivot and join:

SELECT PATIENT_NUM, -0.010+SUM(CE.COEFF*CAST(VALUE AS INT)) AS Predicted_score
FROM (
select patient_num, MDVisit_pname2, MDVisit_pname3, MedicalExam, Mammography, PapTest, PSATest, Colonoscopy, FecalOccultTest, FluShot, PneumococcalVaccine
  , BMI, A1C, meduse1, meduse2, INP1_OPT1_Visit, OPT2_Visit, Num_Dx1, Num_Dx2, ED_Visit, Routine_Care_2
from #cohort
)U
unpivot /* ORACLE EQUIV : https://www.oracletutorial.com/oracle-basics/oracle-unpivot/ */
(value for field_name in (MDVisit_pname2, MDVisit_pname3, MedicalExam, Mammography, PapTest, PSATest, Colonoscopy, FecalOccultTest, FluShot, PneumococcalVaccine
  , BMI, A1C, meduse1, meduse2, INP1_OPT1_Visit, OPT2_Visit, Num_Dx1, Num_Dx2, ED_Visit, Routine_Care_2))p
  JOIN XREF_LoyaltyCode_PSCoeff CE
    ON P.FIELD_NAME = CE.FIELD_NAME
GROUP BY PATIENT_NUM

i2b2plugins / loyalty_cohort

Predicated Score query block #4