Solved

sql to remove records with different date/time?

Posted on 2012-04-10
11
328 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 36
What's wrong with this T-SQL Foreign Key? 7 46
[SQL server / powershell] bulk delete table from CSV 8 33
SQL view 2 27
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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