Solved

sql to remove records with different date/time?

Posted on 2012-04-10
11
332 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
[X]
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
11 Comments
 
LVL 74

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

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

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 74

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 74

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

710 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