Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need SQL help

Posted on 2011-03-23
1
Medium Priority
?
248 Views
Last Modified: 2012-05-11
I am trying to write a sql statement perhaps CTE that will find the earliest contact_date or there is a diferrence between the episde dates of greater than six months. Should I do a stored procedure an break it up?    Here's what I have so far. Thanks

;with CTEMembersLBP as
 (SELECT *, row_number() over(partition by Pat_ID order by Pat_ID, contact_date asc) as Dup
FROM dbo.GR_LBP_DX_2010)
Delete from CTEmembersLBP where Dup > 1  
0
Comment
Question by:Wonderwall
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35202400
the ORDER BY does not need the Pat_ID ...

;with CTEMembersLBP as
 (SELECT *, row_number() over(partition by Pat_ID order by contact_date asc) as Dup
FROM dbo.GR_LBP_DX_2010)
Delete from CTEmembersLBP where Dup > 1  

apart from that, if you have a (clustered) index on Pat_ID + contact_date, this shall work best.

otherwise, if you really have to delete massive data, you might insert the data you want to retain into a temp table, truncate the original table, then insert back the data you want to retain from the temp table
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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