Solved

Query to delete rows

Posted on 2013-06-07
7
264 Views
Last Modified: 2013-06-13
I have a tabled defined as:

create table #YearlyTreatmentData(
 
  TreatmentYear int,
  EyeId nvarchar(18),
  TreatedWith nvarchar(30),
  TreatmentCount int );

Open in new window


The table holds the yearly treatment details for a patients with the type of treatment as TreatedWith, and the number of times treated that year with the treatment as TreatmentCount.

EyeId is a patient id.

I need a Query that will delete all the rows where a patient(EyeId) has been treated by anything other than the specific treatment i am interested in.

If i am looking for patients treated with PDT then i want to keep the patients that have ONLY been treated with PDT. The rest must be deleted.

Patients treated with something else, or patients that have been treated with PDT and something else must be deleted from the table.

I guess (using logic from my previous question) i must delete patients where the sum of all their treatments does not equal the sum of their treatments with PDT i.e. the patients that have changed treatment.

And then also delete every row that is not PDT i.e. the patients treated with something else-

Just dont know how to express this as a delete statement.

Thanks
0
Comment
Question by:soozh
7 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39228974
you mean to say this


delete from #table1
where TreatedWith <> 'PDT'
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39228980
delete from #YearlyTreatmentData
where
   treatedwith <> N'PDT';
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39228986
As a completely unrelated to the answer question, why don't you filter the inserts into the temp table based on the treatedwith column?
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: 39229015
no that is not the answer!

Ofcourse i know how to delete patients that dont have PDT!

I need to delete patients that have PDT + something else, and all those that only have something else.

Your solution only deletes the second group - patients who have been treated with something else.

However i first need to delete patients that have changed treatment.
0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 167 total points
ID: 39229098
Is there only one treatedWith value per entry (will there be multiple rows with the same eyeID if they were treated with something else ) ?

Edit:

If yes, I think the following would work:

delete from YTD
from
   #YearlyTreatmentData as YTD
      left join #YearlyTreatmentData as YTDO on YTD.eyeID = YTDO.eyeID and YTDO.treatedWith <> YTD.treatedWith
   where
      YTDO.eyeID is not null or
      YTD.treatedWith <> N'PDT'; 

Open in new window


(if you have any sample data, that would be quite helpful for testing =))

Edit of Edit:

I changed the query to remove an extraneous filter.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 39229184
delete from yearlytreatmentdata
where eyeid in
(select eyeid from
 (select eyeid from yearlytreatmentdata where treatedwith <> 'PDT')
 union
 (select eyeid from yearlytreatmentdata where treatedwith = 'PDT'
  intersect
  select eyeid from yearlytreatmentdata where treatedwith <> 'PDT')
);
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 166 total points
ID: 39229743
you can also try this.
DELETE FROM #yearlytreatmentdata t1 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   #yearlytreatmentdata t2 
                   WHERE  t1.EyeId = t2.EyeId 
                   GROUP  BY t2.EyeId 
                   HAVING COUNT(DISTINCT t2.TreatedWith) = 1 
                          AND MAX(t2.TreatedWith) = 'PDT') 

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 rs - Sum each category by month 4 32
Query still returning duplicates 5 29
convert null in sql server 12 34
Better way to make a query with date filter. 5 26
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 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