Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.
Solved
Delete unwanted rows
Posted on 2013-06-15
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?
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5 Comments
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
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')
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)
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month9 days, 5 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.