Solved

Delete unwanted rows

Posted on 2013-06-15
5
230 Views
Last Modified: 2013-07-01
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?
0
Comment
Question by:soozh
5 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 250 total points
ID: 39250522
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

0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39250869
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

0
 
LVL 32

Accepted Solution

by:
ewangoya earned 250 total points
ID: 39251119
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

0
 
LVL 24

Expert Comment

by:chaau
ID: 39252003
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
0
 
LVL 31

Expert Comment

by:awking00
ID: 39253464
delete from treatmentdata
where patientid in
(select patientid from treatmentdata where treatedwith = 'NoTreatment'
 except
 select patientid from treatmentdata where treatedwith <> 'NoTreatment')
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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