Not deleting my dates correctly

Question

Not deleting my dates correctly

krypto69

SSChampion

Points: 13604
More actions
April 30, 2010 at 1:55 pm

#232234

I can't seem to be able to find the problem with the below script.
It deletes records but doesn't delete the dates correctly. For example if I put in 10 days to retain it doesn't delete upt to that point.
DECLARE@CUTOFFDATEDATETIME
DECLARE @TEMPID AS TABLE(ID INT)
DECLARE @DaysToRetain SMALLINT
SET @daystoretain = 10
SET @CUTOFFDATE = DATEADD(dd, -(@DaysToRetain), GETDATE())
INSERT INTO @tempid
(ID)
SELECT userclientmetadataid FROM dbo.LPBSecurity_LoginActivity
WHERE CREATEDDATE < @CUTOFFDATE
AND
UserClientMetaDataId NOT IN (SELECT USERCLIENTMETADATAID FROM dbo.LPBSecurity_UserClientSignature)
DELETE FROM dbo.LPBSecurity_LoginActivity
WHERE userclientmetadataid IN (SELECT ID FROM @tempid)
DELETE FROM dbo.LPBSecurity_PhoneFactorChallenge
WHERE userclientmetadataid IN (SELECT ID FROM @tempid)
DELETE FROM dbo.LPBSecurity_RSAAnalysis
WHERE userclientmetadataid IN (SELECT ID FROM @tempid)
DELETE FROM dbo.LPBSecurity_UserClientMetaData
WHERE userclientmetadataid IN (SELECT ID FROM @tempid)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Is it the time portion of the date that causes the incomplete delete? Getdate will return a time portion also and subtracting days will leave the time portion intact on the result. So potentially, not everything on the last date will be deleted, just the stuff added before the leftover time on that date.

And then again, I might be wrong ...
David Webb

Steven James Gray Mr or Mrs. 500 Points: 501 More actions · Answer 2

When you subtract X days from GETDATE(), you will get a residual number of hours, minutes, milliseconds etc depending on how far through the day you are.

If you want to round off to just the day, you can convert to a string and shave off those fields and then convert back:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(255), GETDATE(),102))

This gives you todays date in this format:

2010-04-30 00:00:00.000 (HH:MM:SS.mmmm will always be 0's)

krypto69 SSChampion Points: 13604 More actions · Answer 3

Thanks guys for answering.

I could be wrong, but I don't think it's the time.

It is deleting many records. But when I do a min/max on createddate it is always the same for all three tables.

but my counts show that many records have been deleted...if it was the time it wouldn't delete the records, would it?

I missing something..it seems simple enough but really throwing me..

Steven James Gray Mr or Mrs. 500 Points: 501 More actions · Answer 4

Just because you're deleting records that are linked to another table, nothings to say there might be records in the other tables are are inconsistent/incorrect. For example, what if the userclientmetadataid was null or never ended up in your LPBSecurity_LoginActivity table - the code would never try cleaning up those records.

krypto69 SSChampion Points: 13604 More actions · Answer 5

fwiw- I've looked at the remaining records and they look correct..

no nulls, etc

shoudln't it delete every record less then that date?

Steven James Gray Mr or Mrs. 500 Points: 501 More actions · Answer 6

It'll delete records associated to users with a created date prior to the timestamp being used.

Have you tried doing an inner join between a few of these tables to see what's actually there with the high/low values?

krypto69 SSChampion Points: 13604 More actions · Answer 7

hmm...no i haven't

really didn't think I needed to create any joins because I'm checking for max/min date on createddate

my assumption was that I would not see any dates < my days to retain

Steven James Gray Mr or Mrs. 500 Points: 501 More actions · Answer 8

I mean, rather than trying to figure out what's wrong with the SQL, if you wrote some queries to join the tables together and see why they're not being deleted, and also what records are making up the min/max entries.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 9

I would say the following snippet of your code will probably keep all deletes from happening...

AND

UserClientMetaDataId NOT IN (SELECT USERCLIENTMETADATAID FROM dbo.LPBSecurity_UserClientSignature)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krypto69 SSChampion Points: 13604 More actions · Answer 10

You lost me Jeff....

I need that code to do my deletes...dont' I?