[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query to delete data

Posted on 2013-06-13
6
Medium Priority
?
200 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 25

Accepted Solution

by:
chaau earned 1000 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 41

Assisted Solution

by:Sharath
Sharath earned 1000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 25

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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