Solved

Query to delete data

Posted on 2013-06-13
6
192 Views
Last Modified: 2013-07-01
I have asked a similar question to this and appologise if you feel you are answering the same question!

I have table with patient treatments. It contains the patient id and the treatment they have had.

create table #TreatmentData(
 
  PatientId nvarchar(18),
TreatedWith nvarchar(30) );

Open in new window


There are 7 possible treatments.  We can call them A-G.


PatientId    TreatedWith
1                  A
2                  A
3                  B
1                  C
2                  C
4                  E
4                  E
1                  B

Open in new window

Patients can have any combination of treatments but i am only interested in those that have had "A" and "C"

So i need to delete all patients that have had any other combination of treatments.  

In the table above patient 1 is deleted because he has had treatments A, C and B.
Patient 2 has only had treatments A and C so thats ok.
Patient 3 has had treatment B so is deleted.
Patient 4 has had treatment E so is deleted.

Can someone help with the query?
0
Comment
Question by:soozh
  • 3
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 250 total points
ID: 39246669
This should do:
DELETE FROM Table1
WHERE PatientID NOT IN
(SELECT PatientID,
COUNT(DISTINCT TreatedWith) AS cnt 
FROM Table1
WHERE TreatedWith IN ('A','C') 
AND 
PatientID NOT IN (SELECT PatientID FROM Table1 WHERE TreatedWith NOT IN ('A','C'))
GROUP BY PatientID
HAVING COUNT(DISTINCT TreatedWith) = 2)

Open in new window

0
 

Author Comment

by:soozh
ID: 39246776
Thanks.

Quite simple maybe.  

What does "HAVING COUNT( DISTINCT TreatedWith) = 2" do?
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 39246798
Can you try this?
DELETE FROM #TreatmentData t1 
 WHERE NOT EXISTS (SELECT 1 
                     FROM #TreatmentData t2 
                    WHERE t1.PatientID = t2.PatientID 
                    GROUP BY t2.PatientID 
                   HAVING COUNT(DISTINCT t2.TreatedWith) = 2 
                          AND MIN(t2.TreatedWith) = 'A' 
                          AND MAX(t2.TreatedWith) = 'C') 

Open in new window

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:soozh
ID: 39246932
ok now i understand the "2" is because we have two treatments.
0
 

Author Comment

by:soozh
ID: 39246936
Using chaau's solution i get a syntax error on the line

HAVING COUNT(DISTINCT TreatedWith) = 2

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '2'.

Open in new window


Any ideas why?
0
 
LVL 24

Expert Comment

by:chaau
ID: 39247218
I think because your column is defined as nvarchar you need to use N'A' syntax. Can you change it in your database and retest?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now