somesql.sql

bmi.sql

SELECT * INTO OPENCoronaTempTables..test_bmi_codes FROM (

  SELECT Patient_ID, CTV3Code, NumericValue, ConsultationDate
  FROM CodedEvent
  WHERE CTV3Code IN ('X76C7','22A..', 'XM01E','229..', '22K..')

) t

gistfile1.txt

            -- Uploading codelist for ethnicity
            CREATE TABLE #tmp1_ethnicity_codelist (
              code VARCHAR(5) COLLATE Latin1_General_BIN,
              category VARCHAR(MAX)
            )
            
GO

INSERT INTO #tmp1_ethnicity_codelist (code, category) VALUES
('134B.', '1'),
('Y9930', '1'),
('9S1..', '1'),
('XaFwD', '1'),
('XaIui', '1'),
('XaJQv', '1'),
('XaQEa', '1'),
('XactH', '1'),
('9SA9.', '1'),
('XaFwE', '1'),
('XaJQw', '1'),
('XaJSN', '1'),
('XaQEb', '1'),
('XactI', '1'),
('Xacuu', '1'),
('Y9932', '1'),
('9SAA.', '1'),
('9SAB.', '1'),
('9SAC.', '1'),
('9T11.', '1'),
('XE2Nz', '1'),
('XE2O0', '1'),
('XM1SF', '1'),
('XM1SG', '1'),
('XM1SH', '1'),
('XM1SI', '1'),
('XS7AU', '1'),
('XaFwF', '1'),
('XaFx2', '1'),
('XaIuh', '1'),
('XaJQx', '1'),
('XaJRC', '1'),
('XaJRD', '1'),
('XaJRE', '1'),
('XaJRF', '1'),
('XaJRG', '1'),
('XaJRg', '1'),
('XaJRh', '1'),
('XaJRi', '1'),
('XaJRj', '1'),
('XaJRk', '1'),
('XaJRl', '1'),
('XaJRm', '1'),
('XaJSB', '1'),
('XaJSC', '1'),
('XaJSD', '1'),
('XaJSE', '1'),
('XaJSF', '1'),
('XaJSG', '1'),
('XaJSH', '1'),
('XaJSI', '1'),
('XaJSJ', '1'),
('XaJSK', '1'),
('XaJSL', '1'),
('XaJSM', '1'),
('XaJSO', '1'),
('XaJSP', '1'),
('XaJSQ', '1'),
('XaR4o', '1'),
('XaR4p', '1'),
('XaR61', '1'),
('XaVw5', '1'),
('XaW8w', '1'),
('XactJ', '1'),
('XactK', '1'),
('XacuQ', '1'),
('XacuR', '1'),
('Xacus', '1'),
('Xacut', '1'),
('Xacuv', '1'),
('Xacux', '1'),
('Xacuy', '1'),
('XaedN', '1'),
('XaedQ', '1'),
('XaedS', '1'),
('XaedT', '1'),
('XaedU', '1'),
('XaedV', '1'),
('XaedW', '1'),
('Y1527', '1'),
('XaIB5', '2'),
('XaJQy', '2'),
('XactL', '2'),
('XacuS', '2'),
('134L.', '2'),
('XaIB6', '2'),
('XaJQz', '2'),
('Xactd', '2'),
('XacuT', '2'),
('9SB2.', '2'),
('XaJR0', '2'),
('Xacte', '2'),
('XacuU', '2'),
('134J.', '2'),
('9S5..', '2'),
('9S5..', '2'),
('9S51.', '2'),
('9S51.', '2'),
('9S52.', '2'),
('9S52.', '2'),
('9SB..', '2'),
('9SB1.', '2'),
('9SB3.', '2'),
('9SB4.', '2'),
('XaFwG', '2'),
('XaJR1', '2'),
('XaJRH', '2'),
('XaJRI', '2'),
('XaJRJ', '2'),
('XaJRK', '2'),
('XaJRL', '2'),
('XaJRM', '2'),
('XaJRN', '2'),
('XaJRY', '2'),
('Xactf', '2'),
('Xacua', '2'),
('Xacuz', '2'),
('Y9931', '3'),
('9S6..', '3'),
('9SA7.', '3'),
('XaJR2', '3'),
('Xactg', '3'),
('Xacuc', '3'),
('Xacv2', '3'),
('134M.', '3'),
('9S7..', '3'),
('XaJR3', '3'),
('Xacth', '3'),
('Xacui', '3'),
('Xacv0', '3'),
('134I.', '3'),
('9S8..', '3'),
('XaJR4', '3'),
('Xacti', '3'),
('Xacuj', '3'),
('Xacv5', '3'),
('9SA6.', '3'),
('9SA8.', '3'),
('9T1B.', '3'),
('9T1E.', '3'),
('XE2Ny', '3'),
('XM1SD', '3'),
('XM1SE', '3'),
('XaFwz', '3'),
('XaFx0', '3'),
('XaJR5', '3'),
('XaJRO', '3'),
('XaJRP', '3'),
('XaJRQ', '3'),
('XaJRR', '3'),
('XaJRS', '3'),
('XaJRT', '3'),
('XaJRU', '3'),
('XaJRV', '3'),
('XaJRW', '3'),
('XaW95', '3'),
('Xactk', '3'),
('Xacul', '3'),
('XacvG', '3'),
('134H.', '4'),
('134K.', '4'),
('9S2..', '4'),
('9S42.', '4'),
('9SA3.', '4'),
('XE2Nt', '4'),
('XE2Nw', '4'),
('XM1S8', '4'),
('XM1S9', '4'),
('XaBz7', '4'),
('XaJR6', '4'),
('XacvJ', '4'),
('9S3..', '4'),
('9SA5.', '4'),
('XM1S3', '4'),
('XaJR7', '4'),
('XaJST', '4'),
('XacvH', '4'),
('XacvI', '4'),
('9S4..', '4'),
('9S41.', '4'),
('9S43.', '4'),
('9S44.', '4'),
('9S45.', '4'),
('9S46.', '4'),
('9S47.', '4'),
('9S48.', '4'),
('XE2Nu', '4'),
('XE2Nv', '4'),
('XM1S4', '4'),
('XM1S5', '4'),
('XM1S6', '4'),
('XM1S7', '4'),
('XM1SA', '4'),
('XaBz8', '4'),
('XaFwH', '4'),
('XaFwy', '4'),
('XaJR8', '4'),
('XaJRX', '4'),
('XaJRZ', '4'),
('XaJRa', '4'),
('XaJRb', '4'),
('Xactl', '4'),
('Xactm', '4'),
('Xactn', '4'),
('Xacum', '4'),
('Xacun', '4'),
('Xacuo', '4'),
('XacvZ', '4'),
('Xacva', '4'),
('9T1C.', '5'),
('9T1C.', '5'),
('XaJR9', '5'),
('Xactj', '5'),
('Xacuk', '5'),
('XacvF', '5'),
('134C.', '5'),
('134D.', '5'),
('134E.', '5'),
('134F.', '5'),
('134G.', '5'),
('X76Gp', '5'),
('9SA..', '5'),
('9SA1.', '5'),
('9SA2.', '5'),
('9SA4.', '5'),
('9SAD.', '5'),
('9T1..', '5'),
('9T12.', '5'),
('9T13.', '5'),
('9T14.', '5'),
('9T15.', '5'),
('9T16.', '5'),
('9T17.', '5'),
('9T18.', '5'),
('9T19.', '5'),
('9T1A.', '5'),
('9T1Y.', '5'),
('9T1Z.', '5'),
('XE2Nx', '5'),
('XM1SB', '5'),
('XM1SC', '5'),
('XaE4A', '5'),
('XaFx1', '5'),
('XaJRA', '5'),
('XaJRc', '5'),
('XaJRd', '5'),
('XaJRe', '5'),
('XaJRf', '5'),
('XaJSR', '5'),
('XaJSS', '5'),
('XaJSU', '5'),
('XaJSV', '5'),
('XaJSW', '5'),
('XaJSX', '5'),
('XaJSY', '5'),
('XaJSZ', '5'),
('XaJSa', '5'),
('XaJSb', '5'),
('XaJSc', '5'),
('XaJSd', '5'),
('XaJSe', '5'),
('XaJSf', '5'),
('XaJSg', '5'),
('XaN9x', '5'),
('Xacto', '5'),
('Xactp', '5'),
('Xacup', '5'),
('Xacuq', '5'),
('Xacvb', '5'),
('Xacvc', '5')
GO

-- Query for ethnicity
SELECT * INTO OPENCoronaTempTables..test_ethnicity_codes FROM (
              SELECT CodedEvent.Patient_ID, CTV3Code, ConsultationDate
              FROM CodedEvent
              INNER JOIN #tmp1_ethnicity_codelist
              ON CTV3Code = #tmp1_ethnicity_codelist.code
            ) t
GO

smoking.sql

CREATE TABLE #tmp12_smoking_status_date_codelist (
              code VARCHAR(5) COLLATE Latin1_General_BIN,
              category VARCHAR(MAX)
            )

INSERT INTO #tmp12_smoking_status_date_codelist (code, category) VALUES
('1377.', 'E'),
('1378.', 'E'),
('1379.', 'E'),
('137A.', 'E'),
('137B.', 'E'),
('137F.', 'E'),
('137K.', 'E'),
('137N.', 'E'),
('137O.', 'E'),
('137T.', 'E'),
('137l.', 'E'),
('Ub0p1', 'E'),
('Ub1na', 'E'),
('XE0oj', 'E'),
('XE0ok', 'E'),
('XE0ol', 'E'),
('XE0om', 'E'),
('XE0on', 'E'),
('XE0op', 'E'),
('Xa1bv', 'E'),
('XaQ8V', 'E'),
('XaQUI', 'E'),
('XaQzw', 'E'),
('XaXP6', 'E'),
('XaXP8', 'E'),
('XaXP9', 'E'),
('Y0984', 'E'),
('Y1152', 'E'),
('Y1153', 'E'),
('Y6626', 'E'),
('Y6627', 'E'),
('Y6628', 'E'),
('1371.', 'N'),
('137L.', 'N'),
('Ub0oq', 'N'),
('XE0oh', 'N'),
('XaQUC', 'N'),
('XaXYo', 'N'),
('Y0993', 'N'),
('Y099f', 'N'),
('Y09a0', 'S'),
('1372.', 'S'),
('1373.', 'S'),
('1374.', 'S'),
('1375.', 'S'),
('1376.', 'S'),
('137H.', 'S'),
('137J.', 'S'),
('137M.', 'S'),
('137P.', 'S'),
('137Q.', 'S'),
('137R.', 'S'),
('E251.', 'S'),
('E2510', 'S'),
('E2511', 'S'),
('E2512', 'S'),
('E251z', 'S'),
('Eu17.', 'S'),
('Eu170', 'S'),
('Eu171', 'S'),
('Eu172', 'S'),
('Eu175', 'S'),
('Eu176', 'S'),
('Eu177', 'S'),
('Eu17y', 'S'),
('Eu17z', 'S'),
('H3101', 'S'),
('Ua2Cl', 'S'),
('Ub1tR', 'S'),
('Ub1tS', 'S'),
('Ub1tT', 'S'),
('Ub1tU', 'S'),
('Ub1tV', 'S'),
('Ub1tW', 'S'),
('X20Qm', 'S'),
('XE0oi', 'S'),
('XE0oq', 'S'),
('XE0or', 'S'),
('XE1b4', 'S'),
('XaBSp', 'S'),
('XaIIu', 'S'),
('XaIkW', 'S'),
('XaIkX', 'S'),
('XaIkY', 'S'),
('XaItg', 'S'),
('XaJX2', 'S'),
('XaLQh', 'S'),
('XaQUB', 'S'),
('XaWNE', 'S'),
('XagO3', 'S'),
('Y0983', 'S'),
('Y0994', 'S'),
('Y19d7', 'S'),
('Y2869', 'S'),
('Y3985', 'S'),
('Y7110', 'S'),
('Y7467', 'S'),
('Y7677', 'S'),
('Y9843', 'S'),
('YA602', 'S'),
('ZV6D8', 'S')
GO

-- Query for smoking_status_date
SELECT * INTO OPENCoronaTempTables..test_smoking_codes FROM (
            SELECT
              Patient_ID, CTV3Code, ConsultationDate
            FROM CodedEvent
            INNER JOIN #tmp12_smoking_status_date_codelist
            ON CTV3Code = #tmp12_smoking_status_date_codelist.code
) t
GO