If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Loop to compare dates

I want to loop through a table that has two separate date fields ("EWIT_SHUTDOWN_TIME" and "SHUTDOWN_TIME"). I want to delete all "SHUTDOWN_TIME" fields where it's date difference to the "EWIT_SHUTDOWN_TIME" is less than 20000 milliseconds.
I know I need two loops inside a cursor or maybe I should use two temporary tables? I know what I have to do, just some advice on which is the best approach would be great, thanks.

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Apologies, when I said I wanted to to delete all "SHUTDOWN_TIME" fields, I mean I want to delete the entire row that has this field.
Basically I have a table that contains an audit id, a pc profile id, a shutdown time and an "ewit shutdown time" (four fields).The shutdown time and "ewit shutdown time" are never populated in one row, it is always either one or the other in a single row, not both.
I want to loop through the table and everywhere there are two separate rows that has a shutdown time and an "ewit shutdown time" that are within 20000 milliseconds of each other, I want to delete the one that has the "shutdown time".
Here is what I have so far, a cursor with another cursor inside it. The outer cursor is supposed to collect all the "ewit shutdown times" and the inner one is supposed to (at the same time) collect all the "shutdown times" and if there is a datediff of less than 200, then delete it (I haven't put in that part yet, I am just printing them at the moment).
I am getting an error ("A cursor with the name 'ewit_cursor' does not exist") though.

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

I have changed my code to execute the two cursors one after the other. I have created two tables; one to store the results of the first cursor (TBL_PC_AUDIT_SHUTDOWN) and the second to store the results of the second cursor (TBL_PC_AUDIT_EWIT).

Code:

use CCCNew
go
DECLARE @theewitdate datetime
declare @theewitpcprofile int
DECLARE @theshutdowndate datetime
declare @theshutdownpcprofile int
DECLARE @SQLSDOWN VARCHAR
DECLARE ewit_cursor CURSOR READ_ONLY FOR
SELECT
PC_PROFILE_ID,
EWIT_SHUTDOWN_TIME
FROM
TBL_PC_AUDIT
where EWIT_SHUTDOWN_TIME is not null
OPEN ewit_cursor
FETCH NEXT FROM ewit_cursor INTO @theewitpcprofile,@theewitdate
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM ewit_cursor INTO @theewitpcprofile,@theewitdate
INSERT INTO TBL_PC_AUDIT_EWIT VALUES (@theewitdate,@theewitpcprofile)
end
close ewit_cursor
deallocate ewit_cursor
DECLARE shutdown_cursor CURSOR READ_ONLY FOR
SELECT
PC_PROFILE_ID,
SHUTDOWN_TIME
FROM
TBL_PC_AUDIT
where SHUTDOWN_TIME is not null
OPEN shutdown_cursor
FETCH NEXT FROM shutdown_cursor INTO @theshutdownpcprofile,@theshutdowndate
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM shutdown_cursor INTO @theshutdownpcprofile,@theshutdowndate
INSERT INTO TBL_PC_AUDIT_SHUTDOWN VALUES (@theshutdowndate,@theshutdownpcprofile)
end
close shutdown_cursor
deallocate shutdown_cursor

Then I do a select statement to compare the two tables so I can find the date differences but it does not give back the correct results.

SELECT a.SHUTDOWN_TIME
FROM TBL_PC_AUDIT AS a
WHERE a.SHUTDOWN_TIME IS NOT NULL
AND NOT EXISTS (SELECT *
FROM TBL_PC_AUDIT AS z
WHERE z.EWIT_SHUTDOWN_TIME
BETWEEN a.SHUTDOWN_TIME AND
DATEADD(MS, 20000, a.SHUTDOWNTIME))

This probably will turn up an additional row of data, due to a logic problem in your cursor driven code. It ought to be much faster, and doesn't require the screatch tables either.

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.

You seem to think cursors and temp tables are the answer to your problem. I won't deny you can solve it that way. As you could also write a stored procedure around it and define a few UDF's to make it more complex and harder to maintain.
I will not even consider addressing the problems you encounter by insisting to use the most complex and inefficient way to solve this easy problem.

Cursors are about the last tool I use to solve a SQL-problem. They are slow, create locks, ... Whenever you can avoid them, you should.

Give my code a try and come back with any problems it created or didn't solve.

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

I think you have the best chance of convincing him as your code looks more complex than mine

But it has no cursors, nor temp tables, so I might still have a chance

Alonzo,
Don't get me wrong. It is good that you learn how to use (nested) cursors and temp tables. You have probably learned a few things already by programming them. You need those skills in your tool belt for those occasions when you have no alternative.
You also have to know when to use them and when there are better alternatives available.

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

The code below gives me what I want, I just need to change the two tables I am entering the data from the cursors (" tbl_pc_audit_shutdown" and " tbl_pc_audit_ewit") to temporary tables.
The very last select block I need to change also so that it deletes the "distinct s.AUDIT_ID", not just selects it.
In answer to your questions I am going to wrap it in a stored procedure as its purpose is to catch duplicates. I will have to schedule it to run every 24 hours.
It should not matter that the cursor is slow and creates locks as it will execute at night.
The one thing I am not sure of is that in the table I am testing it on; the row where there is a date diff that I want to delete always comes immediately after a row with the same PC_profile ID.
This will not always be the case as there could be other rows inbetween with different PC_Profile_IDs.

It's always surprising to see people post questions, ignore the answers given, and forge steadily ahead with their bad plans regardless of what accumulated years of experience are unanimous in telling them.

It's always surprising to see people post questions, ignore the answers given, and forge steadily ahead with their bad plans regardless of what accumulated years of experience are unanimous in telling them.

I didn't ignore it, I am just under a bit of pressure to get it done. My boss thinks "patience" is something you find in a hospital.