Solved

sql to remove records with different date/time?

Posted on 2012-04-10
11
322 Views
Last Modified: 2012-04-11
Hi,

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

tblDataNameArchive(id,name,createdate)
x2,xxxx,2012-04-09 11:45:52.900
y5,xxxx,2012-04-09 11:45:52.780
0
Comment
Question by:dkim18
  • 5
  • 4
  • 2
11 Comments
 
LVL 73

Expert Comment

by:sdstuber
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.
0
 

Author Comment

by:dkim18
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.
0
 

Author Comment

by:dkim18
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
0
 

Author Comment

by:dkim18
ID: 37827391
As I said, there will only two different times.
0
 
LVL 73

Expert Comment

by:sdstuber
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dkim18
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.
0
 
LVL 4

Expert Comment

by:agusacil
ID: 37827469
Hi,

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

Author Comment

by:dkim18
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.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 37827529
SELECT *
  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)
0
 
LVL 73

Expert Comment

by:sdstuber
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.
0
 
LVL 4

Assisted Solution

by:agusacil
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
go

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

21 Experts available now in Live!

Get 1:1 Help Now