Solved

Need SQL help

Posted on 2011-03-23
1
225 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
[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
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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