Solved

Query to delete data

Posted on 2013-06-13
6
194 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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