Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
daveb
Starting Member
8 Posts |
 Posted - 2009-03-11 : 05:36:32
|
| Hello everyone,I am in need of some assistance in writing my SQL to complete a task as my knowledge of this language is limited. This is a lengthy message, so beware.I am working with state Medicaid data that is stored in several MS SQL tables, those being the Recipient, Eligibility, Medical Header, Pharmacy Header, Pharm Detail, ICD, Drug, Providers, and Procedure.The investigator needs are: Exclude any patient with a diagnosis of cancer (ICD codes 140-239) or ICD = 344. ICD codes are varchar(6).Exclude patients with a drug class V1A-V1F or Q5N. Both the drug and the ICD exclusions must have taken place from Jan 1, 2008 through Oct 31, 2008.Select all patients who have received a controlled drug within the past 6 months (DEA code = 2, 3, 4, or 5Patient must have be eligible as of Oct 31, 2008The problem I am having is that I can eliminate the individual invoice that meets the exclusion criteria, but I need to eliminate the PERSON from the pool. How does one do that?To further complicate matters, the patients have separate invoices for medical claims and pharmacy claims. This means that I have two separate queries to execute: one to exclude those with the medical claims (ICD exclusion) and another for the pharmacy/drug claims. How do I delete persons (not just individual claims) from both queries?If necessary, I can include my code but since this is already a lengthy message I decided not to include it.Thanks for your consideration in this matter.Dave |
|
|
guptam
Posting Yak Master
161 Posts |
Posted - 2009-03-13 : 01:13:24
|
| Yes please Dave include your code ... I think I understand what your saying but your code will make it easier to understand.Thanks.-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ |
 |
|
|
daveb
Starting Member
8 Posts |
Posted - 2009-03-13 : 04:32:50
|
| Thanks for replying.I am attaching a brief example of my situation instead of code...this should make it easier for you to understand my situatuion.Recipient Table has ID=1 and ID=2 (also contains name and age) Only 1 record per ID Contains column called person key that links table to eligibility table and Medical Header Eligibility Table has ID=1 and ID=2, many records for each ID…one per month, many months in the table for each month the person is eligible for Medicaid. Person key is used to link recipient and medical header tables Medical Header Table contains one record per visit for each person; many to one. Person key is used to link recipient and eligibility tables. Invoice ID is used to link ICD9 table and CPT table.ID=1 INVOICE ID=5, INVOICE ID = 6, INVOICE ID = 7; ID=2 INVOICE ID=11, INVOICE ID =12. The dates on these invoices may/may not be on the same day ICD 9 table contains many records for each person. Invoice ID is used to link back to Medical Header Table. INVOICE ID=5 ICD value=’cancer’; INVOICE ID=6 ICD value = “ok”; INVOICE ID=7 ICD value=”ok”. INVOICE ID=11 ICD value = “ok”; INVOICE ID=12 ICD value = “ok”. The dates on these invoices may/may not be on the same day CPT Table contains many records for each person. Invoice ID is used to link back to Medical Header Table. INVOICE ID=5 CPT value=123; INVOICE ID=6 CPT value = 122 INVOICE ID=7 CPT value122. INVOICE ID=11 CPT value = 22; INVOICE ID=12 CPT value = 11. The dates on these invoices may/may not be on the same day. Expected Results (people/IDs no cancer):Person key with ID=1 would be deleted as he has a cancer code. I would have ID=2 ONLY in the resulting table. The columns would be select items from the recipient table (person key, recip id, name and age), eligibility table (end date), medical header table (date, invoice id), cpt table (icd value and invoice id and date) and cpt table (cpt value and invoice id and invoice date).Therefore, I would have ID=2 and invoice id =11 and invoice id = 12 (two records, ONE ID) The same logic would exist for the drug table, only deleting IDs with certain drugs. This would be using the pharmacy claims. The next problem is how to ensure that I don’t have an ID/PERSON that has been deemed “no cancer” in the process above dealing with medical claims yet found to have been prescribed a “bad” drug in the pharmacy claims. Do I need to identify those medical claims and pharmacy claims to identify PERSONS (not invoices) that should be EXCLUDED and then go back and process the claims again to exclude those IDs? How do you do that? Thanks again for your considerationDave |
|
|
|
guptam
Posting Yak Master
161 Posts |
Posted - 2009-03-13 : 19:53:07
|
| Okay I would do it this way ...Create a temp table and insert into it all patients id that excluded because of your criteria. So ...1) Create Table #ExcludedPatient (PatientID int)2) Create Index idxPatientID ON #XcludedPatient(PatientID)3) INSERT INTO #ExcludedPatient SELECT PatientID FROM [medical claims] (that need to be excluded with your complete join/exclusion criteria).4) INSERT INTO #ExcludedPatient SELECT PatientID FROM [pharmacy/drug claims] (that need to be excluded with your complete join/exclusion criteria).5) Select all the information want with an addition criteria WHERE PatientID NOT IN (SELECT DISTINCT PatientID FROM #ExcludedPatient).Hope I got it right ...Thanks.-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ |
|
|
|
daveb
Starting Member
8 Posts |
Posted - 2009-03-16 : 04:55:02
|
| Thanks so much for your suggestions...that is exactly what I needed.Dave |
|
|
|
|
|
|
|
|
Help with excluding subjects from a query