Created
April 12, 2022 14:32
-
-
Save aaronabend/d07fae455ba641d1f117556e6e491b5f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Autoimmune Disease Prevalence | |
| Autoimmune Registry Inc. | |
| Aaron Abend, 6 April 2022 | |
| Computes prevalence of 159 Autoimmune diseases | |
| Sections in sort: | |
| 10: Denominator and sex/age breakdowns | |
| 20: Comparators: Hypertension, T2D, Hyperlipidemia | |
| 30 & 40: Computing using Autoimmune disease NOS and codes for specific diseases | |
| 50: Autoimmune disease by category | |
| Algorithm: 2 codes 90 days apart with the same SNOMED Code | |
| Antibody (classic autoimmune) | |
| Genetic (not really autoimmune - immune dysfunction due to genetics) | |
| TCell (often classified as autoinflammatory) | |
| Subtypes (these are antibody diseases that are subtypes of antibody driven diseases) | |
| Immune-mediated - immune dysfunction is involved but not really auto-antibody or TCell | |
| Unconfirmed - these are not considered autoimmune diseases - yet | |
| Full list and documentation provided in spreadsheet | |
| 60: Same as 50 but | |
| Algorithm: 2 codes 90 days apart with the same SNOMED Code | |
| OR | |
| 1 codes with a disease and 1 code Autoimmune NOS 90 days apart | |
| 70: Disease-by-disease | |
| 75: Disease-by-disease with sex breakdown | |
| 80: this is extra right now - reporting on all diseases with counts less than 20 for use with ALl of Us program database | |
| */ | |
| declare @denom numeric(10,4)=0; | |
| select @denom=count(distinct pt) from ( | |
| select co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co | |
| group by co.person_id) pts | |
| where ddif>=14 ;-- seen at least 2 times over at least 14 days | |
| with ads AS -- virtual table for list of autoimmune diseases including evidence classification and publication-based prevalencer | |
| ( | |
| select '' disease, '' snomed,'' evidence, ' ' "Prev/Incid", 0 pubprev, 0 "per-100k" union all | |
| select 'Restless legs','32914008','Unconfirmed','P',54450000,16500 union all | |
| select 'Secondary Raynaud''s phenomenon','356198000','Unconfirmed','P',17803500,5395 union all | |
| select 'Psoriasis','9014002','TCELL','P',10395000,3150 union all | |
| select 'Chronic fatigue syndrome','52702003','Unconfirmed','P',7524000,2280 union all | |
| select 'Fibromyalgia','203082005','Unconfirmed','P',5758500,1745 union all | |
| select 'Celiac disease','396331005','Antibody','P',2887500,875 union all | |
| select 'Autoimmune urticaria','402397006','Antibody','P',2640000,800 union all | |
| select 'Hidradenitis suppurativa','59393003','Unconfirmed','P',2260500,685 union all | |
| select 'Crohn''s disease','34000006','TCELL','P',2259675,684.75 union all | |
| select 'Graves'' disease','353295004','Antibody','P',2075700,629 union all | |
| select 'Ulcerative colitis','64766004','Immune-mediated','P',1848825,560.25 union all | |
| select 'Rheumatoid arthritis','69896004','Antibody','P',1848000,560 union all | |
| select 'Autoimmune thyroiditis','66944004','Antibody','P',1815000,550 union all | |
| select 'Pernicious anemia','84027009','Antibody','P',1629375,493.75 union all | |
| select 'Idiopathic pulmonary fibrosis','700250006','Unconfirmed','P',1149555,348.35 union all | |
| select 'Type 1 diabetes mellitus','46635009','Antibody','P',990000,300 union all | |
| select 'Sjögren''s syndrome','83901003','Antibody','P',957000,290 union all | |
| select 'Ankylosing spondylitis','9631008','Unconfirmed','P',858000,260 union all | |
| select 'Endometriosis (clinical)','129103003','Unconfirmed','P',806250,244.318181818182 union all | |
| select 'Multiple sclerosis','24700007','Antibody','P',791296,239.786666666667 union all | |
| select 'Polymyalgia rheumatica','65323003','Immune-mediated','P',785961,238.17 union all | |
| select 'Alopecia areata','68225006','Antibody','P',693000,210 union all | |
| select 'Psoriatic arthritis','156370009','Unconfirmed','P',511500,155 union all | |
| select 'Vitiligo','56727007','Antibody','P',495000,150 union all | |
| select 'Thyroid eye disease','276177000','Subtype','P',518925,157.25 union all | |
| select 'Pediatric autoimmune neuropsychiatric disorder associated with streptococcal infection','446682003','Unconfirmed','P',370000,112.121212121212 union all | |
| select 'Uveitis','128473001','TCELL','P',340560,103.2 union all | |
| select 'Microscopic colitis','235753003','Unconfirmed','P',339900,103 union all | |
| select 'Reactive arthritis','201736002','Immune-mediated','P',304095,92.15 union all | |
| select 'Undifferentiated connective tissue disease','239918008','Unconfirmed','I',254100,77 union all | |
| select 'Cutaneous lupus erythematosus','7119001','Antibody','p',250800,76 union all | |
| select 'Systemic lupus erythematosus','55464009','Antibody','P',241395,73.15 union all | |
| select 'Temporal arteritis','400130008','TCELL','P',232815,70.55 union all | |
| select 'Sarcoidosis','31541009','Unconfirmed','P',196713,59.61 union all | |
| select 'Episcleritis','815008','Subtype','P',173580,52.6 union all | |
| select 'Lichen sclerosus et atrophicus','25674000','Immune-mediated','P',165000,50 union all | |
| select 'Anti-Sperm Antibodies','NOCODE-SPERMANTIBODIES','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Primary idiopathic dilated cardiomyopathy','53043001','Unconfirmed','P',120450,36.5 union all | |
| select 'Acute febrile mucocutaneous lymph node syndrome','75053002','Antibody','P',115170,34.9 union all | |
| select 'Eosinophilic esophagitis','235599003','Unconfirmed','P',110550,33.5 union all | |
| select 'Acute lichenoid pityriasis','86487001','Unconfirmed','P',100454.545454545,30.4407713498623 union all | |
| select 'Primary biliary cholangitis','31712002','Antibody','P',96690,29.3 union all | |
| select 'Postmyocardial infarction syndrome','66189004','Immune-mediated','I',89000,26.969696969697 union all | |
| select 'Bullous pemphigoid','77090002','Antibody','P',85470,25.9 union all | |
| select 'Atrophic gastritis','84568007','Antibody','P',79200,24 union all | |
| select 'Myocarditis due to autoimmune disease','871640001','Unconfirmed','P',77550,23.5 union all | |
| select 'Systemic sclerosis','89155008','Antibody','P',74250,22.5 union all | |
| select 'Evans syndrome','75331009','Subtype','P',71280,21.6 union all | |
| select 'Dermatomyositis','396230008','Antibody','P',70686,21.42 union all | |
| select 'SLE glomerulonephritis syndrome','68815009','Subtype','P',70100.25,21.2425 union all | |
| select 'Complex regional pain syndrome','128200000','Unconfirmed','P',67881,20.57 union all | |
| select 'Myasthenia gravis','91637004','Antibody','P',57750,17.5 union all | |
| select 'Warm autoimmune hemolytic anemia','3978000','Antibody','P',56100,17 union all | |
| select 'Autoimmune hepatitis','408335007','Antibody','P',55770,16.9 union all | |
| select 'Chronic interstitial cystitis','197834003','Unconfirmed','P',54615,16.55 union all | |
| select 'Granulomatosis with polyangiitis','195353004','Antibody','P',52800,16 union all | |
| select 'Autoimmune disorder of inner ear','232308006','TCELL','P',49500,15 union all | |
| select 'Myositis','26889001','Unconfirmed','P',46695,14.15 union all | |
| select 'Narcolepsy','60380001','Antibody','P',46200,14 union all | |
| select 'Autoimmune encephalitis','95643007','Subtype','P',45210,13.7 union all | |
| select 'Polymyositis','31384009','Antibody','P',44550,13.5 union all | |
| select 'Addison''s disease','363732003','Antibody','P',43345.5,13.135 union all | |
| select 'Limbic encephalitis with N-methyl-D-aspartate receptor antibodies','716684004','Antibody','P',37950,11.5 union all | |
| select 'Juvenile rheumatoid arthritis','410795001','Antibody','P',33041,10.0124242424242 union all | |
| select 'Cutaneous mastocytosis','397012002','Unconfirmed','P',33000,10 union all | |
| select 'Felty''s syndrome','57160007','Subtype','P',33000,10 union all | |
| select 'Dermatitis herpetiformis','111196000','Subtype','P',30855,9.35 union all | |
| select 'Microscopic polyangiitis','829821000000102','Antibody','P',30855,9.35 union all | |
| select 'Lupus vasculitis','239944008','Subtype','P',26589.75,8.0575 union all | |
| select 'Chronic idiopathic thrombocytopenic purpura','13172003','Antibody','P',23100,7 union all | |
| select 'Subacute bacterial endocarditis','73774007','Unconfirmed','I',21450,6.5 union all | |
| select 'Primary sclerosing cholangitis','197441003','TCELL','P',20080.5,6.085 union all | |
| select 'Pyoderma gangrenosum','74578003','Unconfirmed','P',19305,5.85 union all | |
| select 'Behcet''s syndrome','310701003','Antibody','P',17160,5.2 union all | |
| select 'Scleritis','78370002','Unconfirmed','P',17160,5.2 union all | |
| select 'Pemphigus vulgaris','49420001','Antibody','P',16995,5.15 union all | |
| select 'Rheumatic fever','58718002','Antibody','I',16500,5 union all | |
| select 'Autoimmune pancreatitis','448542008','Immune-mediated','P',15180,4.6 union all | |
| select 'Immunoglobulin A vasculitis','191306005','Antibody','I',14886.09375,4.5109375 union all | |
| select 'Leukocytoclastic vasculitis','266264003','Immune-mediated','I',14850,4.5 union all | |
| select 'IgA nephropathy','236407003','Immune-mediated','P',12540,3.8 union all | |
| select 'Antisynthetase syndrome','445187004','Antibody','P',11550,3.5 union all | |
| select 'Chronic inflammatory demyelinating polyradiculoneuropathy','128209004','TCELL','P',11121,3.37 union all | |
| select 'Erythema nodosum','32861005','Unconfirmed','P',9900,3 union all | |
| select 'Mixed collagen vascular disease','398049005','Subtype','P',9728,2.94787878787879 union all | |
| select 'Polyneuropathy associated with monoclonal immunoglobulin M antibodies to myelin-associated glycoprotein','718213001','Antibody','P',8283,2.51 union all | |
| select 'Neuromyelitis optica','25044007','Antibody','P',8118,2.46 union all | |
| select 'Transverse myelopathy syndrome','16631009','Subtype','P',8118,2.46 union all | |
| select 'Childhood type dermatomyositis','1212005','Antibody','P',7260,2.2 union all | |
| select 'Steroid-responsive encephalopathy associated with autoimmune thyroiditis','771271000','Subtype','P',6930,2.1 union all | |
| select 'Intermediate uveitis','314429009','TCELL','P',5610,1.7 union all | |
| select 'Guillain-Barré syndrome','40956001','Antibody','I',5412,1.64 union all | |
| select 'Brachial plexus disorder','3548001','Unconfirmed','P',5412,1.64 union all | |
| select 'Cold autoimmune hemolytic anemia','398937006','Antibody','P',5346,1.62 union all | |
| select 'Fasciitis with eosinophilia syndrome','24129002','Unconfirmed','P',5115,1.55 union all | |
| select 'Morphea','201049004','Unconfirmed','P',5115,1.55 union all | |
| select 'Retroperitoneal fibrosis','49120005','Unconfirmed','P',4620,1.4 union all | |
| select 'Paraneoplastic cerebellar degeneration','192877007','Antibody','P',4037.88,1.2236 union all | |
| select 'Cryptogenic organizing pneumonia','719218000','Antibody','I',3630,1.1 union all | |
| select 'Inclusion body myositis','72315009','Unconfirmed','P',3300,1 union all | |
| select 'Immunoglobulin G4 related disease','10743271000119103','Antibody','P',2640,0.8 union all | |
| select 'Autoimmune Angioedema','402401003','Unconfirmed','P',1980,0.6 union all | |
| select 'Adult onset Still''s disease','239920006','Antibody','p',1815,0.55 union all | |
| select 'Autoimmune neutropenia','234425008','Unconfirmed','P',1650,0.5 union all | |
| select 'Takayasu''s disease','359789008','Unconfirmed','P',1551,0.47 union all | |
| select 'Relapsing polychondritis','72275000','Antibody','P',1155,0.35 union all | |
| select 'Acute disseminated encephalomyelitis','83942000','Antibody','I',1105.5,0.335 union all | |
| select 'POEMS syndrome','79268002','Unconfirmed','P',990,0.3 union all | |
| select 'Eaton-Lambert syndrome','56989000','Antibody','P',973.5,0.295 union all | |
| select 'Acute motor axonal neuropathy','715770009','Antibody','I',815.506849315069,0.247123287671233 union all | |
| select 'Aplastic anemia','306058006','TCELL','P',775.5,0.235 union all | |
| select 'Autoimmune oophoritis','721198006','Immune-mediated','P',660,0.2 union all | |
| select 'Polyarteritis nodosa','155441006','Unconfirmed','P',528,0.16 union all | |
| select 'Acquired hemophilia','785308008','Antibody','I',495,0.15 union all | |
| select 'Progressive hemifacial atrophy','718224004','Unconfirmed','P',471.428571428571,0.142857142857143 union all | |
| select 'Linear IgA dermatosis','95330001','Antibody','P',462,0.14 union all | |
| select 'Paroxysmal nocturnal hemoglobinuria','1963002','Unconfirmed','P',412.5,0.125 union all | |
| select 'Benign mucous membrane pemphigoid','34250006','Antibody','I',334.95,0.1015 union all | |
| select 'Anti-glomerular basement membrane tubulointerstitial nephritis','62853008','Antibody','I',330,0.1 union all | |
| select 'Stiff-man syndrome','5217008','Antibody','P',330,0.1 union all | |
| select 'Schnitzler syndrome','402415001','Unconfirmed','P',300,0.0909090909090909 union all | |
| select 'Tolosa-Hunt syndrome','95794005','Unconfirmed','P',300,0.0909090909090909 union all | |
| select 'Antineutrophil cytoplasmic antibody positive vasculitis','722191003','TCELL','I',220,0.0666666666666667 union all | |
| select 'Autoimmune lymphoproliferative syndrome','702444009','Genetic','P',200,0.0606060606060606 union all | |
| select 'Autoimmune enteropathy','235728001','Antibody','P',100,0.0303030303030303 union all | |
| select 'Herpes gestationis','86081009','Antibody','I',100,0.0303030303030303 union all | |
| select 'Sympathetic uveitis','75315001','Antibody','I',99,0.03 union all | |
| select 'Rheumatoid vasculitis','400054000','Subtype','P',14.775,0.00447727272727273 union all | |
| select 'Acquired epidermolysis bullosa','2772003','Antibody','I',85.8,0.026 union all | |
| select 'Lipomatosis dolorosa','71404003','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Antiphospholipid syndrome','26843008','Antibody','P',148500,45 union all | |
| select 'Autoimmune orchitis','NOCODE-ORCHITIS','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Polyglandular autoimmune syndrome, type 1','11244009','Genetic','P',50,0.0151515151515152 union all | |
| select 'Polyglandular autoimmune syndrome, type 2','83728000','Subtype','P',50,0.0151515151515152 union all | |
| select 'Autoimmune polyendocrine syndrome type 3','449731009','Subtype','P',50,0.0151515151515152 union all | |
| select 'Autoimmune progesterone dermatitis','838551007','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Autoimmune retinopathy','724809006','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Balo concentric sclerosis','230380005','Subtype','P',50,0.0151515151515152 union all | |
| select 'Bickerstaff''s brainstem encephalitis','427086003','Antibody','P',50,0.0151515151515152 union all | |
| select 'Lyme disease','23502006','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Cogan''s syndrome','405810005','Antibody','P',50,0.0151515151515152 union all | |
| select 'Enthesitis','359643005','Subtype','P',50,0.0151515151515152 union all | |
| select 'Essential mixed cryoglobulinemia','239947001','Immune-mediated','P',50,0.0151515151515152 union all | |
| select 'Lichen planus','4776004','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Ligneous conjunctivitis','403435005','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Mooren''s ulcer','22440001','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Neuromyotonia','305719002','Antibody','P',50,0.0151515151515152 union all | |
| select 'Palindromic rheumatism','50442003','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Pure red cell aplasia','50715003','Antibody','P',50,0.0151515151515152 union all | |
| select 'Retinocochleocerebral vasculopathy','702575003','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Rheumatic Chorea','46826000','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Systemic mast cell disease','397016004','Unconfirmed','P',50,0.0151515151515152 union all | |
| select 'Opsoclonus-myoclonus syndrome','230350000','Immune-mediated','P',24.79,0.00751212121212121 union all | |
| select 'Acute hemorrhagic leukoencephalitis','72986009','Subtype','I',22.11,0.0067 union all | |
| select 'Epilepsy','84757009','Unconfirmed','',0,0 union all | |
| select 'Age related macular degeneration','267718000','Unconfirmed','P',0,0 union all | |
| select 'Post-Acute Sequelae of COVID-19','1119303003','Unconfirmed','P',0,0 | |
| ) | |
| ,adsplus as ( | |
| select * from ads | |
| union all | |
| select 'Autoimmune Disease NOS','85828009','Antibody','P',0,0 | |
| ) | |
| , pdata as (select person_id, gender.concept_name sex, race.concept_name race | |
| from person p, | |
| concept gender , | |
| concept race | |
| where p.gender_concept_id = gender.concept_id | |
| and p.race_concept_id = race.concept_id) | |
| --Denominator | |
| select 10 SORT, 'Denominator' Description, '' secondarysort, '' tertiarysort ,0.0 pubprev, count(distinct pt) ptcount, convert(int, 330000000*(count(*)/@denom)) USPrev | |
| from ( | |
| select co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co | |
| group by co.person_id) pts | |
| where ddif>=14 -- seen at least 2 times over at least 14 days | |
| UNION all | |
| -- SEX BREAKDOWN | |
| select 10 SORT, 'Denominator', 'Sex',sex, 0,count(distinct pt), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co | |
| group by co.person_id) pts, pdata | |
| where pts.pt = pdata.person_id | |
| and ddif>=14 -- seen at least 2 times over at least 14 days | |
| group by sex | |
| UNION all | |
| -- RACE BREAKDOWN | |
| select 10 SORT, 'Denominator', 'Race',race,0, count(distinct pt), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co | |
| group by co.person_id) pts, pdata | |
| where pts.pt = pdata.person_id | |
| and ddif>=14 -- seen at least 2 times over at least 14 days | |
| group by race | |
| UNION all | |
| -- Comparator -> Hypertension | |
| select 20, concept_name,'', '',0, count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select concept_name, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = '59621000' | |
| group by concept_name, co.person_id) pts | |
| where ddif>=14 -- seen at least 2 times at least 14 days apart | |
| group by concept_name | |
| UNION all | |
| --Comparator -> Hyperlipidemia | |
| select 20, concept_name, '', '',0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select concept_name, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = '55822004' | |
| group by concept_name, co.person_id) pts | |
| where ddif>=14 -- seen at least 2 times at least 90 days apart | |
| group by concept_name | |
| UNION all | |
| --Comparator -> DM2 | |
| select 20, concept_name, '', '',0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select concept_name, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = '44054006' | |
| group by concept_name, co.person_id) pts | |
| where ddif>=14 -- seen at least 2 times at least 90 days apart | |
| group by concept_name | |
| UNION all | |
| -- Count of patients coded with generic autoimmune Disease (NOS - Not Otherwise Specified) | |
| select 30, concept_name, 'Total Generic AD', '',0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select concept_name, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = '85828009' | |
| group by concept_name, co.person_id) pts | |
| where ddif>=90 -- seen at least 2 times at least 90 days apart | |
| group by concept_name | |
| UNION all | |
| -- Count of patients coded with generic autoimmune Disease (NOS - Not Otherwise Specified) - with SEX | |
| select 35, concept_name, 'Total Generic AD', sex,0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select concept_name,sex, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, pdata | |
| where c.concept_id = ca.ancestor_concept_id | |
| and co.person_id = pdata.person_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = '85828009' | |
| group by concept_name, co.person_id, sex) pts | |
| where ddif>=90 -- seen at least 2 times at least 90 days apart | |
| group by concept_name, sex | |
| UNION all | |
| -- Count of AD NOS with no specific AD coded | |
| select 40, concept_name, 'Generic but no specific AD coded', '',0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select concept_name, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = '85828009' | |
| and co.person_id not in ( | |
| select co.person_id pt | |
| from condition_occurrence co, concept_ancestor ca, concept c, ads | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = ads.snomed | |
| ) | |
| group by concept_name, co.person_id) pts | |
| where ddif>=90 -- seen at least 2 times at least 90 days apart | |
| group by concept_name | |
| UNION all | |
| -- Count of AD NOS with no specific AD coded with SEX | |
| select 45, concept_name, 'Generic but no specific AD coded', sex,0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select concept_name, sex,co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, pdata | |
| where c.concept_id = ca.ancestor_concept_id | |
| and co.person_id= pdata.person_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = '85828009' | |
| and co.person_id not in ( | |
| select co.person_id pt | |
| from condition_occurrence co, concept_ancestor ca, concept c, ads | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = ads.snomed | |
| ) | |
| group by concept_name, co.person_id, sex) pts | |
| where ddif>=90 -- seen at least 2 times at least 90 days apart | |
| group by concept_name, sex | |
| UNION all | |
| -- Counts By evidence Class | |
| select 50, evidence, '', '',0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select evidence, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, adsplus | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = adsplus.snomed | |
| group by co.person_id, evidence) pts | |
| where ddif>90 -- seen at least 2 times at least 90 days apart | |
| group by evidence | |
| union all | |
| -- Counts By evidence Class - with sex | |
| select 55, evidence, '', sex,0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select evidence, sex, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, adsplus, pdata | |
| where c.concept_id = ca.ancestor_concept_id | |
| and co.person_id = pdata.person_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = adsplus.snomed | |
| group by co.person_id, evidence, sex) pts | |
| where ddif>90 -- seen at least 2 times at least 90 days apart | |
| group by evidence, sex | |
| union all | |
| -- Count of patients with AD NOS and a Specific disease code, grouped by Evidence Class | |
| select 60, evidence, 'AD NOS And Specific Disease Code', '',0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select evidence, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, ads | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = ads.snomed | |
| and co.person_id in ( | |
| select co2.person_id | |
| from condition_occurrence co2, concept_ancestor ca2 | |
| where ca2.descendant_concept_id = co2.condition_concept_id | |
| and ca2.ancestor_concept_id in (select concept_id from concept where concept_code = '85828009') | |
| group by co2.person_id | |
| having count(distinct condition_start_date)>=2 | |
| and datediff(day, Min(condition_start_date), max(condition_start_date)) >=90 | |
| ) | |
| group by co.person_id, evidence) pts | |
| where ddif>90 -- seen at least 2 times at least 90 days apart | |
| group by evidence | |
| union all | |
| -- Count of patients with AD NOS and a Specific disease code, grouped by Evidence Class - With SEX | |
| select 65, evidence, 'AD NOS And Specific Disease Code', sex,0,count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select evidence, sex, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, ads, pdata | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = ads.snomed | |
| and co.person_id = pdata.person_id | |
| and co.person_id in ( | |
| select co2.person_id | |
| from condition_occurrence co2, concept_ancestor ca2 | |
| where ca2.descendant_concept_id = co2.condition_concept_id | |
| and ca2.ancestor_concept_id in (select concept_id from concept where concept_code = '85828009') | |
| group by co2.person_id | |
| having count(distinct condition_start_date)>=2 | |
| and datediff(day, Min(condition_start_date), max(condition_start_date)) >=90 | |
| ) | |
| group by co.person_id, evidence, sex) pts | |
| where ddif>90 -- seen at least 2 times at least 90 days apart | |
| group by evidence, sex | |
| union all | |
| -- Counts By disease for diseases with patient counts 20 and over | |
| select 70, evidence, disease, '',pubprev, count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select evidence,disease, co.person_id pt, pubprev, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, adsplus | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = adsplus.snomed | |
| group by co.person_id, evidence, disease, pubprev) pts | |
| where ddif>=90 -- seen at least 2 times at least 90 days apart | |
| group by evidence, disease, pubprev | |
| having count(*)>=20 | |
| union all | |
| -- with sex breakdown | |
| select 75, evidence, disease, sex,pubprev, count(*), convert(int, 330000000*(count(*)/@denom)) from ( | |
| select evidence,disease, co.person_id pt, sex,pubprev, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, adsplus, pdata | |
| where c.concept_id = ca.ancestor_concept_id | |
| and co.person_id = pdata.person_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = adsplus.snomed | |
| group by co.person_id, evidence, disease, sex, pubprev) pts | |
| where ddif>=90 -- seen at least 2 times at least 90 days apart | |
| group by evidence, disease, sex, pubprev | |
| union all | |
| -- counts under 20 are aggregated and reported by class | |
| select 80, evidence,'Counts under 20', '',0,sum(ct), convert(int, 330000000*(count(*)/@denom)) from | |
| (select evidence, count(*) ct from | |
| ( | |
| select evidence, disease, co.person_id pt, datediff(day, Min(condition_start_date), max(condition_start_date)) ddif , count(distinct condition_start_date) dtct, count(*) rct | |
| from condition_occurrence co, concept_ancestor ca, concept c, adsplus | |
| where c.concept_id = ca.ancestor_concept_id | |
| and ca.descendant_concept_id = co.condition_concept_id | |
| and c.concept_code = adsplus.snomed | |
| group by co.person_id, evidence, disease) pts | |
| where ddif>=90 -- seen at least 2 times at least 90 days apart | |
| group by evidence, disease | |
| having count(*)<20) lowcounts | |
| group by evidence | |
| order by 1, 2, 3 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment