Solved

Query to delete data

Posted on 2013-06-13
6
189 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard 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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 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

18 Experts available now in Live!

Get 1:1 Help Now