text: /*
  TRANSPOSE THE DIAGNOSIS CODE COLUMNS ON THE MC TABLE TO A LONG TABLE (STG.DX)
  
  DEPENDENCIES:
    RAW.MC
    STG.MC (FINAL CLAIM ROW IDs)
  
  RESULT:
    CREATION OF STG.DX WITH ONE ROW PER DIAGNOSIS AND ALL ICD DX CODES IN ONE COLUMN
  */
  
  
  --SELECT DATABASE
  USE APCD;
  GO
  
  -- DDL FOR STG.DX TABLE
  DROP TABLE IF EXISTS STG.DX;
  CREATE TABLE STG.DX (
      PATKEY INT
      ,MHDO_CLAIM INT
      ,VOCABULARY VARCHAR(10)
      ,DX VARCHAR(15)
      ,PRIMARY_FLAG BIT
      ,POA_FLAG BIT
      ,ADMIT_FLAG BIT
      ,RFV_FLAG BIT
      ,ECODE_FLAG BIT
  );
  GO
  
  -- CREATE TEMP TABLE WITH THE SOURCE DATA FOR THE TRANSPOSITION
  -- ONLY INCLUDE DX DATA FROM FINAL CLAIMS WHICH WERE DEFINED IN STG.MC
  DROP TABLE IF EXISTS ##STG_DX_SOURCE;
  SELECT A.* 
  INTO ##STG_DX_SOURCE
  FROM RAW.MC A
  INNER JOIN STG.MC B
  ON A.MC902_IDN = B.IDN;
  -- PROBABLY COULD USE AN INDEX ON IDN
  GO
  
  -- CREATE TEMP TABLES CONTAINING THE TRANSPOSED DX DATA
  DROP TABLE IF EXISTS ##TMP_DX1;
  WITH 
  -- ICD 10 DX
  PRINDGNS AS(
      SELECT DISTINCT
          CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
         -- CAST('ICD10CM' AS VARCHAR(10)) AS VOCABULARY,
          'ICD10CM' AS VOCABULARY,
          MC200_PRINDGNS AS DX, 
          CAST(1 AS BIT) AS PRIMARY_FLAG,
          CAST(CASE WHEN TRIM(MC201_POA) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
          CAST(0 AS BIT) AS ADMIT_FLAG,
          CAST(0 AS BIT) AS RFV_FLAG,
          CAST(0 AS BIT) AS ECODE_FLAG
      FROM ##STG_DX_SOURCE
      WHERE MC200_PRINDGNS IS NOT NULL 
  ),
  ADMDGNS AS(
      SELECT DISTINCT
          CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
          'ICD10CM' AS VOCABULARY,
          MC202_ADMDGNS AS DX,
          CAST(0 AS BIT) AS PRIMARY_FLAG,
          NULL AS POA_FLAG, -- IS ADMITTING DX ALWAYS POA? SHOULD THIS BE NULL?
          CAST(1 AS BIT) AS ADMIT_FLAG,
          CAST(0 AS BIT) AS RFV_FLAG,
          CAST(0 AS BIT) AS ECODE_FLAG
      FROM ##STG_DX_SOURCE
      WHERE MC202_ADMDGNS IS NOT NULL 
  ),
  RFVDGNS1 AS(
      SELECT DISTINCT
          CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
          'ICD10CM' AS VOCABULARY,
          MC203_RFVDGNS1 AS DX,
          CAST(0 AS BIT) AS PRIMARY_FLAG,
          NULL AS POA_FLAG, -- SHOULD THIS BE NULL?
          CAST(0 AS BIT) AS ADMIT_FLAG,
          CAST(1 AS BIT) AS RFV_FLAG,
          CAST(0 AS BIT) AS ECODE_FLAG
      FROM ##STG_DX_SOURCE
      WHERE MC203_RFVDGNS1 IS NOT NULL 
  ),
  RFVDGNS2 AS(
      SELECT DISTINCT
          CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
          'ICD10CM' AS VOCABULARY,
          MC204_RFVDGNS2 AS DX,
          CAST(0 AS BIT) AS PRIMARY_FLAG,
          NULL AS POA_FLAG, -- SHOULD THIS BE NULL?
          CAST(0 AS BIT) AS ADMIT_FLAG,
          CAST(1 AS BIT) AS RFV_FLAG,
          CAST(0 AS BIT) AS ECODE_FLAG
      FROM ##STG_DX_SOURCE
      WHERE MC204_RFVDGNS2 IS NOT NULL 
  ),
  RFVDGNS3 AS(
      SELECT DISTINCT
          CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
          'ICD10CM' AS VOCABULARY,
          MC205_RFVDGNS3 AS DX,
          CAST(0 AS BIT) AS PRIMARY_FLAG,
          NULL AS POA_FLAG, -- SHOULD THIS BE NULL?
          CAST(0 AS BIT) AS ADMIT_FLAG,
          CAST(1 AS BIT) AS RFV_FLAG,
          CAST(0 AS BIT) AS ECODE_FLAG
      FROM ##STG_DX_SOURCE
      WHERE MC205_RFVDGNS3 IS NOT NULL 
  ),
  COMBINED AS(
      SELECT * FROM PRINDGNS UNION
      SELECT * FROM ADMDGNS UNION
      SELECT * FROM RFVDGNS1 UNION
      SELECT * FROM RFVDGNS2 UNION
      SELECT * FROM RFVDGNS3
  )
  SELECT * INTO ##TMP_DX1 FROM COMBINED;
  GO
  
  -- TRANSPOSE ICD10 ECODE COLUMNS
  DROP TABLE IF EXISTS ##TMP_DX2;
  WITH 
  ECOM1 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC206_ECOM1 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC207_POA1) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC206_ECOM1 IS NOT NULL 
  ),
  ECOM2 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC208_ECOM2 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC209_POA2) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC208_ECOM2 IS NOT NULL 
  ),
  ECOM3 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC210_ECOM3 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC211_POA3) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC210_ECOM3 IS NOT NULL 
  ),
  ECOM4 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC212_ECOM4 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC213_POA4) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC212_ECOM4 IS NOT NULL 
  ),
  ECOM5 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC214_ECOM5 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC215_POA5) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC214_ECOM5 IS NOT NULL 
  ),
  ECOM6 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC216_ECOM6 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC217_POA6) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC216_ECOM6 IS NOT NULL 
  ),
  ECOM7 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC218_ECOM7 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC219_POA7) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC218_ECOM7 IS NOT NULL 
  ),
  ECOM8 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC220_ECOM8 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC221_POA8) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC220_ECOM8 IS NOT NULL 
  ),
  ECOM9 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC222_ECOM9 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC223_POA9) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC222_ECOM9 IS NOT NULL 
  ),
  ECOM10 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC224_ECOM10 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC225_POA10) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC224_ECOM10 IS NOT NULL 
  ),
  ECOM11 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC226_ECOM11 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC227_POA11) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC226_ECOM11 IS NOT NULL 
  ),
  ECOM12 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC228_ECOM12 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC229_POA12) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC228_ECOM12 IS NOT NULL 
  ),
  ECOM13 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC230_ECOM13 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC231_POA13) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC230_ECOM13 IS NOT NULL 
  ),
  ECOM14 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC232_ECOM14 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC233_POA14) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC232_ECOM14 IS NOT NULL 
  ),
  ECOM15 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC234_ECOM15 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC235_POA15) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC234_ECOM15 IS NOT NULL 
  ),
  ECOM16 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC236_ECOM16 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC237_POA16) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC236_ECOM16 IS NOT NULL 
  ),
  ECOM17 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC238_ECOM17 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC239_POA17) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC238_ECOM17 IS NOT NULL 
  ),
  ECOM18 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC240_ECOM18 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC241_POA18) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC240_ECOM18 IS NOT NULL 
  ),
  ECOM19 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC242_ECOM19 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC243_POA19) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC242_ECOM19 IS NOT NULL 
  ),
  ECOM20 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC244_ECOM20 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC245_POA20) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC244_ECOM20 IS NOT NULL 
  ),
  ECOM21 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC246_ECOM21 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC247_POA21) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC246_ECOM21 IS NOT NULL 
  ),
  ECOM22 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC248_ECOM22 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC249_POA22) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC248_ECOM22 IS NOT NULL 
  ),
  ECOM23 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC250_ECOM23 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC251_POA23) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC250_ECOM23 IS NOT NULL 
  ),
  ECOM24 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC252_ECOM24 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC253_POA24) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC252_ECOM24 IS NOT NULL 
  ),
  COMBINED AS(
      SELECT * FROM ECOM1 UNION
      SELECT * FROM ECOM2 UNION
      SELECT * FROM ECOM3 UNION
      SELECT * FROM ECOM4 UNION
      SELECT * FROM ECOM5 UNION
      SELECT * FROM ECOM6 UNION
      SELECT * FROM ECOM7 UNION
      SELECT * FROM ECOM8 UNION
      SELECT * FROM ECOM9 UNION
      SELECT * FROM ECOM10 UNION
      SELECT * FROM ECOM11 UNION
      SELECT * FROM ECOM12 UNION
      SELECT * FROM ECOM13 UNION
      SELECT * FROM ECOM14 UNION
      SELECT * FROM ECOM15 UNION
      SELECT * FROM ECOM16 UNION
      SELECT * FROM ECOM17 UNION
      SELECT * FROM ECOM18 UNION
      SELECT * FROM ECOM19 UNION
      SELECT * FROM ECOM20 UNION
      SELECT * FROM ECOM21 UNION
      SELECT * FROM ECOM22 UNION
      SELECT * FROM ECOM23 UNION
      SELECT * FROM ECOM24 
  )
  SELECT DISTINCT * INTO ##TMP_DX2 FROM COMBINED;
  GO
  
  -- TRANSPOSE THE OTHER ICD10 DX CODES
  DROP TABLE IF EXISTS ##TMP_DX3;
  WITH 
  OTHDX1 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC254_OTHDX1 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC255_OTHPOA1) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC254_OTHDX1 IS NOT NULL 
  ),
  OTHDX2 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC256_OTHDX2 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC257_OTHPOA2) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC256_OTHDX2 IS NOT NULL 
  ),
  OTHDX3 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC258_OTHDX3 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC259_OTHPOA3) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC258_OTHDX3 IS NOT NULL 
  ),
  OTHDX4 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC260_OTHDX4 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC261_OTHPOA4) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC260_OTHDX4 IS NOT NULL 
  ),
  OTHDX5 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC262_OTHDX5 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC263_OTHPOA5) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC262_OTHDX5 IS NOT NULL 
  ),
  OTHDX6 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC264_OTHDX6 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC265_OTHPOA6) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC264_OTHDX6 IS NOT NULL 
  ),
  OTHDX7 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC266_OTHDX7 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC267_OTHPOA7) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC266_OTHDX7 IS NOT NULL 
  ),
  OTHDX8 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC268_OTHDX8 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC269_OTHPOA8) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC268_OTHDX8 IS NOT NULL 
  ),
  OTHDX9 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC270_OTHDX9 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC271_OTHPOA9) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC270_OTHDX9 IS NOT NULL 
  ),
  OTHDX10 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC272_OTHDX10 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC273_OTHPOA10) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC272_OTHDX10 IS NOT NULL 
  ),
  OTHDX11 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC274_OTHDX11 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC275_OTHPOA11) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC274_OTHDX11 IS NOT NULL 
  ),
  OTHDX12 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC276_OTHDX12 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC277_OTHPOA12) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC276_OTHDX12 IS NOT NULL 
  ),
  OTHDX13 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC278_OTHDX13 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC279_OTHPOA13) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC278_OTHDX13 IS NOT NULL 
  ),
  OTHDX14 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC280_OTHDX14 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC281_OTHPOA14) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC280_OTHDX14 IS NOT NULL 
  ),
  OTHDX15 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC282_OTHDX15 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC283_OTHPOA15) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC282_OTHDX15 IS NOT NULL 
  ),
  OTHDX16 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC284_OTHDX16 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC285_OTHPOA16) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC284_OTHDX16 IS NOT NULL 
  ),
  OTHDX17 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC286_OTHDX17 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC287_OTHPOA17) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC286_OTHDX17 IS NOT NULL 
  ),
  OTHDX18 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC288_OTHDX18 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC289_OTHPOA18) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC288_OTHDX18 IS NOT NULL 
  ),
  OTHDX19 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC290_OTHDX19 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC291_OTHPOA19) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC290_OTHDX19 IS NOT NULL 
  ),
  OTHDX20 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC292_OTHDX20 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC293_OTHPOA20) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC292_OTHDX20 IS NOT NULL 
  ),
  OTHDX21 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC294_OTHDX21 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC295_OTHPOA21) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC294_OTHDX21 IS NOT NULL 
  ),
  OTHDX22 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC296_OTHDX22 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC297_OTHPOA22) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC296_OTHDX22 IS NOT NULL 
  ),
  OTHDX23 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC298_OTHDX23 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC299_OTHPOA23) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC298_OTHDX23 IS NOT NULL 
  ),
  OTHDX24 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD10CM' AS VOCABULARY,
    MC300_OTHDX24 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    CAST(CASE WHEN TRIM(MC301_OTHPOA24) IN('Y', '1') THEN 1 ELSE 0 END AS BIT) AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC300_OTHDX24 IS NOT NULL 
  ),
  COMBINED AS(
  SELECT * FROM OTHDX1 UNION
  SELECT * FROM OTHDX2 UNION
  SELECT * FROM OTHDX3 UNION
  SELECT * FROM OTHDX4 UNION
  SELECT * FROM OTHDX5 UNION
  SELECT * FROM OTHDX6 UNION
  SELECT * FROM OTHDX7 UNION
  SELECT * FROM OTHDX8 UNION
  SELECT * FROM OTHDX9 UNION
  SELECT * FROM OTHDX10 UNION
  SELECT * FROM OTHDX11 UNION
  SELECT * FROM OTHDX12 UNION
  SELECT * FROM OTHDX13 UNION
  SELECT * FROM OTHDX14 UNION
  SELECT * FROM OTHDX15 UNION
  SELECT * FROM OTHDX16 UNION
  SELECT * FROM OTHDX17 UNION
  SELECT * FROM OTHDX18 UNION
  SELECT * FROM OTHDX19 UNION
  SELECT * FROM OTHDX20 UNION
  SELECT * FROM OTHDX21 UNION
  SELECT * FROM OTHDX22 UNION
  SELECT * FROM OTHDX23 UNION
  SELECT * FROM OTHDX24 
  )
  SELECT DISTINCT * INTO ##TMP_DX3 FROM COMBINED;
  --SELECT COUNT(*) FROM ##TMP_DX3;
  GO
  
  -- TRANSPOSE THE ICD 9 DX CODES
  DROP TABLE IF EXISTS ##TMP_DX4;
  WITH 
  ADMDX AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC039_ADMDX AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(1 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC039_ADMDX IS NOT NULL 
  ),
  ECODE AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC040_ECODE AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(1 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC040_ECODE IS NOT NULL 
  ),
  DX1 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC041_DX1 AS DX, 
    CAST(1 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC041_DX1 IS NOT NULL 
  ),
  DX2 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC042_DX2 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC042_DX2 IS NOT NULL 
  ),
  DX3 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC043_DX3 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC043_DX3 IS NOT NULL 
  ),
  DX4 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC044_DX4 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC044_DX4 IS NOT NULL 
  ),
  DX5 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC045_DX5 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC045_DX5 IS NOT NULL 
  ),
  DX6 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC046_DX6 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC046_DX6 IS NOT NULL 
  ),
  DX7 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC047_DX7 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC047_DX7 IS NOT NULL 
  ),
  DX8 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC048_DX8 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC048_DX8 IS NOT NULL 
  ),
  DX9 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC049_DX9 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC049_DX9 IS NOT NULL 
  ),
  DX10 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC050_DX10 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC050_DX10 IS NOT NULL 
  ),
  DX11 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC051_DX11 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC051_DX11 IS NOT NULL 
  ),
  DX12 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC052_DX12 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC052_DX12 IS NOT NULL 
  ),
  DX13 AS(
    SELECT DISTINCT
    CAST(MC907_MHDO_CLAIM AS INT) AS MHDO_CLAIM, 
    'ICD9CM' AS VOCABULARY,
    MC053_DX13 AS DX, 
    CAST(0 AS BIT) AS PRIMARY_FLAG,
    NULL AS POA_FLAG,
    CAST(0 AS BIT) AS ADMIT_FLAG,
    CAST(0 AS BIT) AS RFV_FLAG,
    CAST(0 AS BIT) AS ECODE_FLAG
    FROM ##STG_DX_SOURCE
    WHERE MC053_DX13 IS NOT NULL 
  ),
  COMBINED AS(
      SELECT * FROM ADMDX UNION
      SELECT * FROM ECODE UNION
      SELECT * FROM DX1 UNION
      SELECT * FROM DX2 UNION
      SELECT * FROM DX3 UNION
      SELECT * FROM DX4 UNION
      SELECT * FROM DX5 UNION
      SELECT * FROM DX6 UNION
      SELECT * FROM DX7 UNION
      SELECT * FROM DX8 UNION
      SELECT * FROM DX9 UNION
      SELECT * FROM DX10 UNION
      SELECT * FROM DX11 UNION
      SELECT * FROM DX12 UNION
      SELECT * FROM DX13 
  )
  SELECT DISTINCT * INTO ##TMP_DX4 FROM COMBINED;
  --SELECT COUNT(*) FROM ##TMP_DX4;
  GO
  
  -- LOAD THE STG.DX TABLE
  TRUNCATE TABLE STG.DX;
  INSERT INTO STG.DX (
      PATKEY,
      MHDO_CLAIM, 
      VOCABULARY,
      DX,
      PRIMARY_FLAG,
      POA_FLAG,
      ADMIT_FLAG,
      RFV_FLAG,
      ECODE_FLAG
  )
  SELECT DISTINCT 
      B.PATKEY,
      A.MHDO_CLAIM, 
      A.VOCABULARY,
      TRIM(A.DX) AS DX,
      A.PRIMARY_FLAG,
      A.POA_FLAG,
      A.ADMIT_FLAG,
      A.RFV_FLAG,
      A.ECODE_FLAG 
  FROM (
      SELECT * FROM ##TMP_DX1 UNION
      SELECT * FROM ##TMP_DX2 UNION
      SELECT * FROM ##TMP_DX3 UNION
      SELECT * FROM ##TMP_DX4
  ) A
  INNER JOIN (SELECT DISTINCT MHDO_CLAIM, PATKEY FROM STG.MC) B 
  ON A.MHDO_CLAIM = B.MHDO_CLAIM;
  
  GO
  
  -- DROP TEMP TABLES
  DROP TABLE IF EXISTS ##TMP_DX1;
  DROP TABLE IF EXISTS ##TMP_DX2;
  DROP TABLE IF EXISTS ##TMP_DX3;
  DROP TABLE IF EXISTS ##TMP_DX4;
  DROP TABLE IF EXISTS ##STG_DX_SOURCE;
  GO
  
  /*
  SELECT COUNT(*) FROM STG.DX;
  SELECT TOP 10 * FROM STG.DX;
  */