sql to remove records with different date/time?


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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
dkim18Author Commented:
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.
dkim18Author Commented:
##,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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

dkim18Author Commented:
As I said, there will only two different times.
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?
dkim18Author Commented:
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.

In that particular example, are those records need to be archived because they don't start with xxxx,2012-04-09 11:47:42 ?
dkim18Author Commented:
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.
  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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.