Solved

Query to delete data

Posted on 2013-06-13
6
187 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
Comment Utility
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
Comment Utility
Thanks.

Quite simple maybe.  

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

Assisted Solution

by:Sharath
Sharath earned 250 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

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

Author Comment

by:soozh
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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