Solved

Delete SQL Records Not Wanted

Posted on 2014-01-16
12
298 Views
Last Modified: 2014-01-17
Table1

Custno          Inactive     Inactivedt      
P123456           N            10/12/2013
P123456           N            11/22/2013
P123456           Y             02/01/2013
P123456           Y             03/01/2013
P456123           N            01/15/2014
P456123           Y             12/10/2013  
P456123           Y             11/04/2013
P789456           Y             01/10/2014
P789456           Y             09/15/2013


How would I delete the same customer numbers that Inactive = Y and only keep 1 of the same customer numbers that is = N  with the latest Inactive date

Or,  if all same customers only have a Inactive = Y, then only keep that customer with the latest date and delete the other one.

Table1  (should look like when done )

Custno          Inactive     Inactivedt      
P123456           N            11/22/2013
P456123           N            01/15/2014
P789456           Y             01/10/2014

So, only keep the latest Inactive = N record with the same customer numbers and delete the rest

or,  if no Inactive = N then only keep the latest Inactive = Y customer number
0
Comment
Question by:thayduck
  • 6
  • 4
  • 2
12 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39786222
Is it more simply the record with the latest Inactivedt?
Either way, one answer using SQL 2008 is to create a CTE that adds a row number column.  The answer to the above will determine the ORDER BY; otherwise, the solution is the same as the final query will be a DELETE that filters out records with RN > 1.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39786241
Here is a self-contained working example of what I mean:
DECLARE @your_table TABLE(
    Custno VARCHAR(10), 
    Inactive CHAR(1), 
    Inactivedt DATE
);

INSERT INTO @your_table(Custno, Inactive, Inactivedt)
VALUES('P123456','N','10/12/2013'),
      ('P123456','N','11/22/2013'),
      ('P123456','Y','02/01/2013'),
      ('P123456','Y','03/01/2013'),
      ('P456123','N','01/15/2014'),
      ('P456123','Y','12/10/2013'),
      ('P456123','Y','11/04/2013'),
      ('P789456','Y','01/10/2014'),
      ('P789456','Y','09/15/2013')
;

;WITH cte AS (
    /* Rank records based on Inactivedt descending. */
    SELECT Custno, Inactive, Inactivedt
         , RN = ROW_NUMBER()
             OVER(PARTITION BY Custno
                  ORDER BY Inactivedt DESC)
    FROM @your_table
)
/* Delete old records. */
DELETE FROM cte WHERE RN > 1;

SELECT Custno, Inactive, Inactivedt
FROM @your_table
;

Open in new window


Results of SELECT after DELETE:
Custno     Inactive Inactivedt
---------- -------- ----------
P123456    N        2013-11-22
P456123    N        2014-01-15
P789456    Y        2014-01-10

Open in new window


I hope that helps!

Best regards and happy coding,

Kevin
0
 

Author Comment

by:thayduck
ID: 39786243
The same customer# could have the same Inactive date where 1 record is a N and the other one is a Y.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 175 total points
ID: 39786268
See if this meets your needs:
DECLARE @your_table TABLE(
    Custno VARCHAR(10), 
    Inactive CHAR(1), 
    Inactivedt DATE
);

INSERT INTO @your_table(Custno, Inactive, Inactivedt)
VALUES('P123456','N','10/12/2013'),
      ('P123456','Y','11/22/2013'), -- same inactivedt as 'N'
      ('P123456','N','11/22/2013'),
      ('P123456','Y','02/01/2013'),
      ('P123456','Y','03/01/2013'),
      ('P456123','N','01/15/2014'),
      ('P456123','Y','12/10/2013'),
      ('P456123','Y','11/04/2013'),
      ('P789456','Y','01/10/2014'),
      ('P789456','Y','09/15/2013')
;

;WITH cte AS (
    /* Rank records based on Inactivedt descending. */
    SELECT Custno, Inactive, Inactivedt
         , RN = ROW_NUMBER()
             OVER(PARTITION BY Custno
                  ORDER BY Inactivedt DESC, Inactive)
    FROM @your_table
)
/* Delete old records. */
DELETE FROM cte WHERE RN > 1;

SELECT Custno, Inactive, Inactivedt
FROM @your_table
;

Open in new window


It uses a secondary sort on the [Inactive] column, so 'N' ranks before 'Y'.  If the real values in the table do not sort naturally, you can use the following.
CASE [Inactive] WHEN 'first choice value' THEN 1 ELSE 2 END

Open in new window


You would insert that in the ORDER BY of the OVER() clause in place of "Inactive" above.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39786619
Is it possible for a custno with an inactive value of 'Y' to have an inactivedt greater than any inactivedt where the inactive value is 'N' for that custno? For example, could this condition exist?
P123456           Y            12/20/2013
P123456           N            10/12/2013
P123456           N            11/22/2013
P123456           Y             02/01/2013
P123456           Y             03/01/2013
If so, what would you want the result to be?
0
 

Author Comment

by:thayduck
ID: 39786688
As long as there is a N record, I want that N record with the latest date no matter what date is in the Y record.

I only want the latest Y record when there is no N record.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:thayduck
ID: 39786721
Kevin, your code is pulling in wrong record.  It is pulling in the 06/08/2013 record.


F149200SC      Y      06/08/2013
P149200SC      N      01/16/2014


F149200SC            Y      2013-06-08   rec it left in table.
0
 

Author Comment

by:thayduck
ID: 39786727
here is code i ran:


 ;WITH cte AS (
    /* Rank records based on Inactivedt descending. */
    SELECT Cust#, Inactive, Inactivedt
         , RN = ROW_NUMBER()
             OVER(PARTITION BY Cust#
                  ORDER BY Inactivedt DESC, Inactive)
    FROM #gpcustwork
)
/* Delete old records. */
DELETE FROM cte WHERE RN > 1;
0
 
LVL 31

Expert Comment

by:awking00
ID: 39786843
>>F149200SC      Y      06/08/2013
P149200SC      N      01/16/2014

F149200SC            Y      2013-06-08   rec it left in table. <<
Because it's partitioning by cust# and F149200SC is a different cust# than P149200SC. If they, in fact, represent the same customer, then you could partition by the substring of the cust# beginning with the second character.
0
 

Author Comment

by:thayduck
ID: 39787400
Man, I am sorry, how did I miss that.

Of course,  they are 2 different cust#.
0
 

Author Closing Comment

by:thayduck
ID: 39787410
Thanks, does just what I need it to do.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39789302
I am glad that helped.  BTW, if you want any 'N' record regardless of 'Y' dates, you can swap the ORDER BY.  Therefore, it will order 'N' before 'Y', then sort by date descending.  Hence, the only way for a 'Y' record to have the rank 1 is for no 'N' records exist.

Best regards,

Kevin
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 46
SQL Backup skipping a few tables 7 25
Extract XML Data from using TSQL 5 30
IN with @variable 5 17
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

19 Experts available now in Live!

Get 1:1 Help Now