Delete unwanted rows

I have a table "TreatmentData" defined as:

create table #TreatmentData(

PatientId nvarchar(18),
TreatedWith nvarchar(30) );

The table lists patient treatments. A patient can have many rows in the table. There are six possible values for TreatedWith.

I need to delete all the patient records where the patient has "NoTreatment" for all his/her rows in the database. i.e. they have visited the clinic but not been treated.

I imagine it must be where the number of rows is equal to the count where the TreatedWith is "NoTreatment" when grouped by PatientId...

but i just can not get my head round it.

Any suggestions?

soozh

Asked: 2013-06-15

Advertise Here

2 Solutions

LVL 16

Surendra NathTechnology LeadCommented: 2013-06-15

the below query might help you out

;with CTE as
(
 select PatientId,TreatedWith  from #TreatmentData group by PatientId,TreatedWith 
), CTE1 AS
(
 select PatiendID from CTE C WHERE NOT EXISTS ( select 1 FROM CTE C1 where C1.PatiendID = C.PatiendID and C1.TreatedWith  <> 'NoTreatment')
)
DELETE T
FROM #TreatmentData T
JOIN CTE1 C
ON T.PatiendID = C.PatiendID

Open in new window

LVL 5

DOSLoverCommented: 2013-06-15

Essentially what we are saying here is that delete the patient record if we find only 'NoTreatment' records for that patient:

Delete from TreatmentData a
 where exists 
       (Select PatientId from TreatmentData b
	     where b.PatientId = a.PatientId
		   and b.TreatedWith = 'NoTreatment')
   and NOT exists 
       (Select PatientId from TreatmentData b
	     where b.PatientId = a.PatientId
		   and b.TreatedWith <> 'NoTreatment')

Open in new window

LVL 32

Ephraim WangoyaCommented: 2013-06-16

Here

with cte as
(
  select *
  from TreatmentData
  where TreatedWith <> 'NoTreatment'
)

delete from TreatMentData
where not exists(select 1 
                           from cte A
                           where TreatmentData.PatientID = A.PatientID)

Open in new window

LVL 25

chaauCommented: 2013-06-16

Just kidding: when you apply all the SQL statements against you treatment table that you have asked during last week, you will probably end up with a nice empty table. Ha-ha

LVL 32

awking00Commented: 2013-06-17

delete from treatmentdata
where patientid in
(select patientid from treatmentdata where treatedwith = 'NoTreatment'
except
select patientid from treatmentdata where treatedwith <> 'NoTreatment')

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

Delete unwanted rows

Join & Write a Comment Already a member? Login.

Featured Post