sql to remove records with different date/time?

Posted on 2012-04-10
Last Modified: 2012-04-11

I have a table with a date field.
I want to write up sql stored procedure that compares against each other to remove records which are a little older than other records.

There will be always only two different times. Probably they will be different dates but I just want to cover in case the dates are same but different times.
I want to find a way to programmatically  search those records and remove into the archive table.

As an example, I have tblDataName and tblDataNameArchive.

tblDataName(id,name,createdate)  <==id is guid
x1,xxxx,2012-04-09 11:47:42.121
x2,xxxx,2012-04-09 11:47:42.126
x2,xxxx,2012-04-09 11:45:52.900
y2,xxxx,2012-04-09 11:47:42.678
y5,xxxx,2012-04-09 11:45:52.780

x2,xxxx,2012-04-09 11:45:52.900
y5,xxxx,2012-04-09 11:45:52.780
Question by:dkim18
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
  • 4
  • 2
LVL 74

Expert Comment

ID: 37827314
I don't understand the selection criteria.

The highlighted x2 row is the earlier time of a pair of for x2  - do you always move the earlier time?

The highlighted y5 row is the only y5 row,  why is it archived but not the others?  x1 and y2 are also lone records.

Author Comment

ID: 37827344
Please ignore id values. I was trying to make them unique and didn't make corrections right.
They should be guid primary keys. The ID field is the primary key.
Don't try to relate them. I didn't mean to.

All I want to do is a way to compare the createdate field and find ones with the older time.
So in this example, I need to find 3rd and 5th records.

Author Comment

ID: 37827352
##,xxxx,2012-04-09 11:47:42.121
##,xxxx,2012-04-09 11:47:42.126
##,xxxx,2012-04-09 11:45:52.900
##,xxxx,2012-04-09 11:47:42.678
##,xxxx,2012-04-09 11:45:52.780

I probably will have different dates like below but I would like to handle the same date just in case.
##,xxxx,2012-04-09 11:47:42.121
##,xxxx,2012-04-09 11:47:42.126
##,xxxx,2012-04-06 11:45:52.900
##,xxxx,2012-04-09 11:47:42.678
##,xxxx,2012-04-06 11:45:52.780
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


Author Comment

ID: 37827391
As I said, there will only two different times.
LVL 74

Expert Comment

ID: 37827426
ok, I think I'm getting closer to understanding.

so, you're looking for 2 records out of all of the data that fall on a different second?  

In your first example you picked the two records that were not   2012-04-09 11:47:42

in your second example you have multiple days, but again, those different days are the two that don't share the same second as the others.

Is that the identifier?

Author Comment

ID: 37827457
different second?   no.
You should compare minutes.

(I am pretty sure I will have different days)

I guess I can use this:

select min(entered_date) from tblDataName;

How do I put that in the where clause?

select * from tblDataName where entered_date =

I guess I can do this in stored procedure then.

Expert Comment

ID: 37827469

In that particular example, are those records need to be archived because they don't start with xxxx,2012-04-09 11:47:42 ?

Author Comment

ID: 37827503
No. They are archived because those records have not been updated so it will have older dates.

I have this application uses this table. Once in a while when there  are new data files, the update database tool read these data files and update this table if there are already records with the same data file name. If no records in the table, it will create a new record.
These data files are consists of previously imported data files.
It has previously read data files and new data files and removed data files.
In case the file is removed. There is still a record in the table which has not been updated since there is no longer that data file.

Because of this, you will always have only two different dated records.
So I am trying to remove those older records.
LVL 74

Accepted Solution

sdstuber earned 400 total points
ID: 37827529
  FROM tbldataname
 WHERE     dateadd(mi, datediff(mi, 0, createdate), 0) =
               (SELECT MIN(dateadd(mi, datediff(mi, 0, createdate), 0)) FROM tbldataname)
       AND dateadd(mi, datediff(mi, 0, createdate), 0) !=
               (SELECT MAX(dateadd(mi, datediff(mi, 0, createdate), 0)) FROM tbldataname)
LVL 74

Expert Comment

ID: 37827545
you can remove the check for != max if you are sure you will always have 2 sets of dates. old and new.

I added the check to make sure you wouldn't match records that simply hadn't been updated yet.

Assisted Solution

agusacil earned 100 total points
ID: 37827720
This script will backup old records into archive table, and delete the record from the main table

declare @lastdatetime as datetime --Variable to get the latest datetime of tblDataName
declare @lastdatetimeminute as datetime -- Latest datetime, up to the minute (ignore the seconds)

select top 1 @lastdatetime = createdate from tblDataName order by createdate desc

select @lastdatetime
set @lastdatetimeminute = dateadd(minute, datediff(MINUTE,0, @lastdatetime), 0)
select @lastdatetimeminute
--Decreaes the lastdatetimeminute with one minute, as a selection criteria for 'old' record
set @lastdatetimeminute = DATEADD(minute, -1, @lastdatetimeminute )
select @lastdatetimeminute
-- Allow insert on identity column for table tblDataNameArchive
SET IDENTITY_INSERT tblDataNameArchive ON --
--Backup to archive, all records that are equal or older than the lastdatetimeminute
insert into tblDataNameArchive(id,name,createdate) select id,name,createdate from tblDataName where createdate <= @lastdatetimeminute
--Delete the record from table
delete from tblDataName where createdate <= @lastdatetimeminute

Open in new window


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

632 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