- From: Eric Prud'hommeaux <eric@w3.org>
- Date: Tue, 19 Aug 2008 11:30:14 -0400
- To: public-hcls-coi@w3.org
- Message-ID: <20080819153014.GA1202@w3.org>
* Eric Prud'hommeaux <eric@w3.org> [2008-07-29 12:02-0400]
> * Eric Prud'hommeaux <eric@w3.org> [2008-07-29 09:52-0400]
> > Jyoti used the RsNromAPI to grabbed 21253 NDC code/ingredient mappings.
> > Of those, 1860 line up with the NDC codes in our Medication_DE file. Of
> > those, how many line up with patients we need to know about? I don't
> > know, but I have the impression that he chose his query set carefully.
>
> I looked up the NDC codes that we need for our patient data. we need
> 133 more NDC codes (the first column is how many times that drug is used):
>
> SELECT COUNT(*),Medication_DE.NDC,Medication_DE.DDI,Medication_DE.EntryName
> FROM Medication
> JOIN Medication_DE ON Medication.MedDictDE=Medication_DE.ID
> LEFT OUTER JOIN NDCcodes ON Medication_DE.NDC=NDCcodes.NDC
> WHERE NDCcodes.NDC IS NULL
> GROUP BY Medication_DE.ID
> ORDER BY Medication_DE.EntryName;
> +---+-------------+--------+--------------------------------------------------+
> |*) | NDC | DDI | EntryName |
> +---+-------------+--------+--------------------------------------------------+
> | 6 | 68115071330 | 79538 | Abilify 10 MG Tablet |
> | 2 | 63874005890 | 234 | Accupril 40 MG Tablet |
> | 7 | 4015549 | 240 | Accutane 10 MG Capsule |
> | 1 | 51927285900 | 44057 | Albuterol Powder |
> | 1 | 68115041130 | 864 | Allopurinol 100 MG Tablet |
> | 2 | 68387048530 | 1002 | Ambien 10 MG Tablet |
> | 1 | 68115083000 | 1003 | Ambien TABS |
> | 2 | 591571301 | 1155 | Amoxapine 25 MG Tablet |
> | 1 | 591571401 | 1156 | Amoxapine TABS |
> | 7 | 68115002530 | 24712 | Amoxicillin 125 MG Tablet Chewable |
> | 3 | 68115090480 | 1160 | Amoxicillin 125 MG/5ML Suspension Reconstituted |
> | 8 | 66116067620 | 60101 | Amoxicillin 200 MG Tablet Chewable |
> | 2 | 63304096904 | 60103 | Amoxicillin 200 MG/5ML Suspension Reconstituted |
> | 5 | 68258100201 | 1157 | Amoxicillin 250 MG Capsule |
> | 2 | 68115002830 | 60102 | Amoxicillin 400 MG Tablet Chewable |
> | 3 | 68387043040 | 1158 | Amoxicillin 500 MG Capsule |
> | 2 | 68387043040 | 1158 | Amoxicillin CAPS |
> | 1 | 38779019008 | 44062 | Amoxicillin Powder |
> | 2 | 54868447000 | 60162 | Amoxil CHEW |
> | 3 | 67544009289 | 47183 | Aricept 10 MG Tablet |
> | 2 | 62856024590 | 47182 | Aricept 5 MG Tablet |
> | 1 | 54868515901 | 1795 | Armour Thyroid TABS |
> | 3 | 90389001751 | 1925 | Aspirin 325 MG Tablet |
> |12 | 64393503916 | 24939 | Aspirin 81 MG Tablet |
> | 1 | 68387053830 | 2012 | Atenolol 50 MG Tablet |
> | 2 | 68115092505 | 62736 | Avelox 400 MG Tablet |
> | 2 | 49999023220 | 2410 | Bactrim TABS |
> | 1 | 68258906201 | 25154 | Benazepril HCl 5 MG Tablet |
> | 2 | 54868376900 | 39102 | Capozide TABS |
> | 3 | 68115005990 | 25724 | Captopril 25 MG Tablet |
> | 5 | 68387055260 | 58748 | Celebrex 200 MG Capsule |
> | 2 | 68115008220 | 4618 | Cipro 500 MG Tablet |
> | 3 | 58016011850 | 4619 | Cipro TABS |
> | 1 | 55390019701 | 121705 | Ciprofloxacin 200 MG Solution |
> | 3 | 68387053401 | 59832 | Ciprofloxacin HCl 750 MG Tablet |
> | 2 | 62991251802 | 88216 | Ciprofloxacin Powder |
> | 2 | 11523719802 | 122444 | Claritin CHEW |
> | 2 | 68115042109 | 26312 | Clindamycin HCl CAPS |
> | 1 | 68115097915 | 4779 | Clotrimazole 1 % Cream |
> | 1 | 63323004401 | 5553 | Cyanocobalamin 1000 MCG/ML Solution |
> | 5 | 24033206 | 6037 | Demerol 50 MG/5ML SYRP |
> | 1 | 66860008002 | 6617 | Digoxin 0.25 MG/ML Solution |
> | 2 | 68115012860 | 27122 | Enalapril Maleate 20 MG Tablet |
> | 2 | 68115012960 | 27123 | Enalapril Maleate 5 MG Tablet |
> | 1 | 58016007090 | 8510 | Flexeril 10 MG Tablet |
> | 3 | 68258910601 | 71005 | Flomax 0.4 MG Capsule Extended Release 24 Hour |
> | 1 | 58016653301 | 73664 | Flonase SUSP |
> | 1 | 55045335100 | 92935 | Flovent HFA 110 MCG/ACT Aerosol |
> | 1 | 68387012090 | 27521 | Fluoxetine HCl 20 MG Capsule |
> | 2 | 64679075909 | 8861 | Furosemide 10 MG/ML Solution |
> | 2 | 60432061360 | 8862 | Furosemide 10 MG/ML Solution (OR) |
> | 2 | 68387036530 | 8864 | Furosemide 20 MG Tablet |
> | 3 | 68258101101 | 8865 | Furosemide 40 MG Tablet |
> | 1 | 904545846 | 65855 | Gas Free Extra Strength 125 MG Capsule |
> | 2 | 68258101301 | 27641 | GlipiZIDE 10 MG Tablet |
> | 2 | 87606413 | 81793 | Glucophage XR 750 MG Tablet Extended Release 24 |
> | 1 | 51927224300 | 87744 | Grape Flavor Liquid |
> | 1 | 67544100960 | 47677 | Hydrochlorothiazide 12.5 MG Capsule |
> | 4 | 68387023560 | 66149 | Hydrocodone-Acetaminophen 10-325 MG Tablet |
> | 1 | 68115039799 | 79777 | Hydrocodone-Acetaminophen 5-325 MG Tablet |
> | 1 | 406435701 | 35031 | Hydrocodone-Acetaminophen 5-500 MG CAPS |
> | 9 | 68387030090 | 40162 | Hydrocodone-Acetaminophen 5-500 MG Tablet |
> | 1 | 68387020821 | 10519 | Ibuprofen 600 MG Tablet |
> | 1 | 68387021090 | 10520 | Ibuprofen 800 MG Tablet |
> | 3 | 58016024609 | 40964 | Imitrex 25 MG Tablet |
> | 1 | 68115090709 | 68073 | Imitrex TABS |
> | 1 | 38415001529 | 78941 | Insulin Syringe 1cc/29Gx1/2"Miscellaneous |
> | 4 | 62175012937 | 64033 | Isosorbide Mononitrate CR 120 MG Tablet Extended |
> | 1 | 58016063390 | 11409 | Keflex 500 MG Capsule |
> | 2 | 68115083690 | 47942 | Lipitor 10 MG Tablet |
> | 2 | 68258900101 | 66713 | Lipitor TABS |
> | 1 | 68387054330 | 28360 | Lisinopril 10 MG Tablet |
> | 3 | 68258105901 | 12248 | Lisinopril 20 MG Tablet |
> | 1 | 62584090311 | 12459 | Loxapine Succinate 50 MG Capsule |
> | 1 | 59440020809 | 84833 | Medadyne Oral Pain Relief 10-0.13-70 % Liquid |
> | 4 | 68462014101 | 50070 | Meloxicam 15 MG Tablet |
> | 1 | 68462014001 | 50053 | Meloxicam 7.5 MG Tablet |
> | 2 | 54854516 | 13221 | Meperidine HCl 50 MG/5ML SYRP |
> | 3 | 68115063200 | 13368 | Methotrexate 2.5 MG Tablet |
> | 1 | 68258108301 | 13447 | Metoprolol Tartrate 100 MG Tablet |
> | 1 | 54868552401 | 28686 | Metoprolol-Hydrochlorothiazide 100-25 MG Tablet |
> | 6 | 68115043760 | 68633 | Mobic 15 MG Tablet |
> | 1 | 63304070601 | 13822 | Morphine Sulfate 10 MG TABS |
> | 2 | 9738502 | 13848 | Motrin 400 MG TABS |
> | 2 | 54868043904 | 13849 | Motrin 600 MG Tablet |
> | 4 | 58864002630 | 13850 | Motrin 800 MG Tablet |
> | 4 | 50580077074 | 13851 | Motrin IB 200 MG Tablet |
> | 8 | 58864002630 | 13850 | Motrin TABS |
> | 1 | 68462019005 | 14432 | Naproxen 500 MG Tablet |
> | 1 | 67871773204 | 52809 | Nascobal 500 MCG/0.1ML Gel |
> | 1 | 67871773305 | 100455 | Nascobal 500 MCG/0.1ML Solution |
> | 3 | 58016082490 | 70045 | Nexium 20 MG Capsule Delayed Release |
> | 1 | 62451330535 | 64337 | Nitroglycerin 0.1 MG/HR Patch 24 Hour |
> | 8 | 0 | 0 | No Current Medications |
> | 2 | 54868471200 | 51079 | Nor-QD 0.35 MG Tablet |
> | 1 | 68382009816 | 29473 | Paroxetine HCl 20 MG Tablet |
> | 1 | 68115091930 | 43371 | Paxil 10 MG Tablet |
> | 3 | 29321548 | 55517 | Paxil 10 MG/5ML Suspension |
> | 9 | 68115026730 | 16385 | Paxil 20 MG Tablet |
> |13 | 67544027060 | 16386 | Paxil 30 MG Tablet |
> | 9 | 68115091630 | 43372 | Paxil TABS |
> | 2 | 70074051121 | 32084 | Phenex-1 POWD |
> | 2 | 68258914901 | 16854 | Phenobarbital TABS |
> | 1 | 68258108101 | 40383 | Prevacid 30 MG Capsule Delayed Release |
> | 2 | 55056040601 | 80836 | Prochieve 4 % GEL |
> | 1 | 54868523300 | 69335 | Protopic 0.1 % Ointment |
> | 3 | 55289030830 | 18058 | Prozac 10 MG Capsule |
> | 4 | 68115071930 | 18059 | Prozac 20 MG Capsule |
> | 3 | 777512058 | 89952 | Prozac 20 MG/5ML Solution |
> | 3 | 54868176002 | 18548 | Retin-A 0.025 % Cream |
> | 1 | 54868250401 | 130610 | Retrovir SYRP |
> | 2 | 108501220 | 20923 | Tagamet 200 MG TABS |
> | 2 | 54868123503 | 21075 | Tegretol 100 MG Tablet Chewable |
> | 1 | 54868234300 | 21542 | Thiothixene 2 MG Capsule |
> | 1 | 66336095160 | 21636 | Thyroid 30 MG Tablet |
> | 2 | 68387055630 | 47395 | Topamax 100 MG Tablet |
> | 1 | 68115077315 | 22082 | Triamcinolone Acetonide 0.1 % Ointment |
> | 1 | 61958070101 | 88902 | Truvada 200-300 MG Tablet |
> | 1 | 45046870 | 22561 | Tylenol 500 MG CAPS |
> | 2 | 50580014860 | 22569 | Tylenol Allergy Sinus 2-30-500 MG Tablet |
> | 1 | 68115087900 | 74529 | Ultracet TABS |
> | 1 | 11509000363 | 23028 | Vagisil 5-2 % Cream (VA) |
> | 2 | 58016006390 | 23369 | Vicodin 5-500 MG Tablet |
> | 1 | 74197354 | 77158 | Vicodin ES 750-7.5 MG TABS |
> | 1 | 58016006390 | 23369 | Vicodin TABS |
> | 2 | 904275452 | 23500 | Vita Zinc Tablet |
> | 1 | 63402051001 | 98975 | Xopenex HFA 45 MCG/ACT Aerosol |
> | 2 | 63402051124 | 75771 | Xopenex NEBU |
> | 1 | 50419040503 | 101196 | YAZ 3-0.02 MG Tablet |
> | 1 | 66116070306 | 70227 | Zithromax Z-Pak 250 MG Tablet |
> | 2 | 68115077790 | 24226 | Zocor 40 MG Tablet |
> | 3 | 68115036560 | 24233 | Zoloft 100 MG Tablet |
> | 4 | 68115036660 | 24234 | Zoloft 50 MG Tablet |
> +---+-------------+--------+--------------------------------------------------+
> 133 rows in set (21.87 sec)
limiting by existing patient prescriptions:
SELECT COUNT(*),Medication_DE.NDC,Medication_DE.DDI,Medication_DE.EntryName
FROM Person
JOIN Item_Medication ON Person.ID=Item_Medication.PatientID
JOIN Medication ON Item_Medication.ID=Medication.ItemID
JOIN Medication_DE ON Medication.MedDictDE=Medication_DE.ID
LEFT OUTER JOIN NDCcodes ON Medication_DE.NDC=NDCcodes.NDC
WHERE NDCcodes.NDC IS NULL
GROUP BY Medication_DE.ID
ORDER BY Medication_DE.EntryName;
-> -> -> -> -> -> -> ->
+----------+-------------+--------+------------------------------------------------------------------+
| COUNT(*) | NDC | DDI | EntryName |
+----------+-------------+--------+------------------------------------------------------------------+
| 3 | 68115071330 | 79538 | Abilify 10 MG Tablet |
| 2 | 63874005890 | 234 | Accupril 40 MG Tablet |
| 7 | 4015549 | 240 | Accutane 10 MG Capsule |
| 1 | 51927285900 | 44057 | Albuterol Powder |
| 2 | 68387048530 | 1002 | Ambien 10 MG Tablet |
| 1 | 68115083000 | 1003 | Ambien TABS |
| 2 | 591571301 | 1155 | Amoxapine 25 MG Tablet |
| 1 | 591571401 | 1156 | Amoxapine TABS |
| 4 | 68115002530 | 24712 | Amoxicillin 125 MG Tablet Chewable |
| 2 | 68115090480 | 1160 | Amoxicillin 125 MG/5ML Suspension Reconstituted |
| 6 | 66116067620 | 60101 | Amoxicillin 200 MG Tablet Chewable |
| 2 | 63304096904 | 60103 | Amoxicillin 200 MG/5ML Suspension Reconstituted |
| 3 | 68258100201 | 1157 | Amoxicillin 250 MG Capsule |
| 1 | 68115002830 | 60102 | Amoxicillin 400 MG Tablet Chewable |
| 3 | 68387043040 | 1158 | Amoxicillin 500 MG Capsule |
| 2 | 68387043040 | 1158 | Amoxicillin CAPS |
| 1 | 38779019008 | 44062 | Amoxicillin Powder |
| 2 | 54868447000 | 60162 | Amoxil CHEW |
| 3 | 67544009289 | 47183 | Aricept 10 MG Tablet |
| 1 | 62856024590 | 47182 | Aricept 5 MG Tablet |
| 3 | 90389001751 | 1925 | Aspirin 325 MG Tablet |
| 10 | 64393503916 | 24939 | Aspirin 81 MG Tablet |
| 1 | 68387053830 | 2012 | Atenolol 50 MG Tablet |
| 2 | 68115092505 | 62736 | Avelox 400 MG Tablet |
| 2 | 49999023220 | 2410 | Bactrim TABS |
| 1 | 68258906201 | 25154 | Benazepril HCl 5 MG Tablet |
| 2 | 54868376900 | 39102 | Capozide TABS |
| 1 | 68115005990 | 25724 | Captopril 25 MG Tablet |
| 4 | 68387055260 | 58748 | Celebrex 200 MG Capsule |
| 2 | 68115008220 | 4618 | Cipro 500 MG Tablet |
| 3 | 58016011850 | 4619 | Cipro TABS |
| 1 | 55390019701 | 121705 | Ciprofloxacin 200 MG Solution |
| 3 | 68387053401 | 59832 | Ciprofloxacin HCl 750 MG Tablet |
| 2 | 62991251802 | 88216 | Ciprofloxacin Powder |
| 2 | 68115042109 | 26312 | Clindamycin HCl CAPS |
| 1 | 63323004401 | 5553 | Cyanocobalamin 1000 MCG/ML Solution |
| 2 | 24033206 | 6037 | Demerol 50 MG/5ML SYRP |
| 1 | 58016007090 | 8510 | Flexeril 10 MG Tablet |
| 3 | 68258910601 | 71005 | Flomax 0.4 MG Capsule Extended Release 24 Hour |
| 1 | 58016653301 | 73664 | Flonase SUSP |
| 1 | 68387012090 | 27521 | Fluoxetine HCl 20 MG Capsule |
| 2 | 68387036530 | 8864 | Furosemide 20 MG Tablet |
| 3 | 68258101101 | 8865 | Furosemide 40 MG Tablet |
| 1 | 904545846 | 65855 | Gas Free Extra Strength 125 MG Capsule |
| 2 | 68258101301 | 27641 | GlipiZIDE 10 MG Tablet |
| 2 | 87606413 | 81793 | Glucophage XR 750 MG Tablet Extended Release 24 Hour |
| 1 | 67544100960 | 47677 | Hydrochlorothiazide 12.5 MG Capsule |
| 4 | 68387023560 | 66149 | Hydrocodone-Acetaminophen 10-325 MG Tablet |
| 1 | 68115039799 | 79777 | Hydrocodone-Acetaminophen 5-325 MG Tablet |
| 9 | 68387030090 | 40162 | Hydrocodone-Acetaminophen 5-500 MG Tablet |
| 1 | 68387021090 | 10520 | Ibuprofen 800 MG Tablet |
| 3 | 58016024609 | 40964 | Imitrex 25 MG Tablet |
| 1 | 68115090709 | 68073 | Imitrex TABS |
| 1 | 38415001529 | 78941 | Insulin Syringe 1cc/29Gx1/2"Miscellaneous |
| 4 | 62175012937 | 64033 | Isosorbide Mononitrate CR 120 MG Tablet Extended Release 24 Hour |
| 1 | 58016063390 | 11409 | Keflex 500 MG Capsule |
| 1 | 68115083690 | 47942 | Lipitor 10 MG Tablet |
| 1 | 68387054330 | 28360 | Lisinopril 10 MG Tablet |
| 1 | 62584090311 | 12459 | Loxapine Succinate 50 MG Capsule |
| 1 | 68462014001 | 50053 | Meloxicam 7.5 MG Tablet |
| 3 | 68115063200 | 13368 | Methotrexate 2.5 MG Tablet |
| 1 | 54868552401 | 28686 | Metoprolol-Hydrochlorothiazide 100-25 MG Tablet |
| 5 | 68115043760 | 68633 | Mobic 15 MG Tablet |
| 1 | 63304070601 | 13822 | Morphine Sulfate 10 MG TABS |
| 2 | 9738502 | 13848 | Motrin 400 MG TABS |
| 2 | 54868043904 | 13849 | Motrin 600 MG Tablet |
| 3 | 58864002630 | 13850 | Motrin 800 MG Tablet |
| 4 | 50580077074 | 13851 | Motrin IB 200 MG Tablet |
| 4 | 58864002630 | 13850 | Motrin TABS |
| 3 | 58016082490 | 70045 | Nexium 20 MG Capsule Delayed Release |
| 2 | 0 | 0 | No Current Medications |
| 1 | 68382009816 | 29473 | Paroxetine HCl 20 MG Tablet |
| 1 | 68115091930 | 43371 | Paxil 10 MG Tablet |
| 1 | 29321548 | 55517 | Paxil 10 MG/5ML Suspension |
| 6 | 68115026730 | 16385 | Paxil 20 MG Tablet |
| 11 | 67544027060 | 16386 | Paxil 30 MG Tablet |
| 7 | 68115091630 | 43372 | Paxil TABS |
| 2 | 70074051121 | 32084 | Phenex-1 POWD |
| 2 | 68258914901 | 16854 | Phenobarbital TABS |
| 1 | 68258108101 | 40383 | Prevacid 30 MG Capsule Delayed Release |
| 2 | 55056040601 | 80836 | Prochieve 4 % GEL |
| 3 | 55289030830 | 18058 | Prozac 10 MG Capsule |
| 4 | 68115071930 | 18059 | Prozac 20 MG Capsule |
| 3 | 777512058 | 89952 | Prozac 20 MG/5ML Solution |
| 2 | 54868176002 | 18548 | Retin-A 0.025 % Cream |
| 2 | 108501220 | 20923 | Tagamet 200 MG TABS |
| 2 | 54868123503 | 21075 | Tegretol 100 MG Tablet Chewable |
| 1 | 54868234300 | 21542 | Thiothixene 2 MG Capsule |
| 1 | 66336095160 | 21636 | Thyroid 30 MG Tablet |
| 2 | 68387055630 | 47395 | Topamax 100 MG Tablet |
| 1 | 61958070101 | 88902 | Truvada 200-300 MG Tablet |
| 1 | 45046870 | 22561 | Tylenol 500 MG CAPS |
| 2 | 50580014860 | 22569 | Tylenol Allergy Sinus 2-30-500 MG Tablet |
| 1 | 68115087900 | 74529 | Ultracet TABS |
| 1 | 11509000363 | 23028 | Vagisil 5-2 % Cream (VA) |
| 2 | 58016006390 | 23369 | Vicodin 5-500 MG Tablet |
| 1 | 74197354 | 77158 | Vicodin ES 750-7.5 MG TABS |
| 1 | 58016006390 | 23369 | Vicodin TABS |
| 1 | 63402051001 | 98975 | Xopenex HFA 45 MCG/ACT Aerosol |
| 1 | 50419040503 | 101196 | YAZ 3-0.02 MG Tablet |
| 2 | 68115036560 | 24233 | Zoloft 100 MG Tablet |
| 4 | 68115036660 | 24234 | Zoloft 50 MG Tablet |
+----------+-------------+--------+------------------------------------------------------------------+
102 rows in set (11.78 sec)
--
-eric
office: +1.617.258.5741 32-G528, MIT, Cambridge, MA 02144 USA
mobile: +1.617.599.3509
(eric@w3.org)
Feel free to forward this message to any list for any purpose other than
email address distribution.
Received on Tuesday, 19 August 2008 15:31:10 UTC