Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2005
    Posts
    30

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by alonzo View Post
    I know I need two loops inside a cursor or maybe I should use two temporary tables?
    Who has planted that complex and inefficient thing in your head? I can't (don't want to) help you with loops and temp tables and cursors to solve this simple problem. But I will give you the easy way.

    It wasn't clear to me what you meant by
    to delete all "SHUTDOWN_TIME" fields
    You can delete records or set columns ("fields") to NULL or another value.

    In case you want to DELETE records:
    Code:
    DELETE 
    FROM DaTable
    WHERE DATEDIFF(millisecond, SHUTDOWN_TIME, EWIT_SHUTDOWN_TIME) < 20000
    In case you want to blanc the SHUTDOWN_TIME column:
    Code:
    UPDATE DaTable
    SET SHUTDOWN_TIME = NULL
    WHERE SHUTDOWN_TIME IS NOT NULL AND
    	DATEDIFF(millisecond, SHUTDOWN_TIME, EWIT_SHUTDOWN_TIME) < 20000
    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

  3. #3
    Join Date
    Feb 2005
    Posts
    30
    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.
    Code:
    use CCCNew
    
    BEGIN
    
    DECLARE @theewitdate datetime
    declare @theewitpcprofile int
    DECLARE @theshutdowndate datetime
    declare @theshutdownpcprofile int
    DECLARE @ewit_cursor CURSOR
    
    set @ewit_cursor = cursor 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 
    print @theewitpcprofile
    print @theewitdate  
    
    		OPEN shutdown_cursor  
    		FETCH NEXT FROM shutdown_cursor INTO @theshutdownpcprofile,@theshutdowndate  
    
    		WHILE @@FETCH_STATUS = 0  
    		BEGIN 
    		print @theshutdownpcprofile
    		print @theshutdowndate  
    		end
    		close shutdown_cursor 
    
    end
    close ewit_cursor 
    deallocate ewit_cursor 
    END
    Last edited by alonzo; 08-26-11 at 07:03.

  4. #4
    Join Date
    Feb 2005
    Posts
    30
    I have cahnged my code to this but I have to tweak it further:
    Code:
    
    
    use CCCNew
    
    go
    
    DECLARE @theewitdate datetime
    declare @theewitpcprofile int
    DECLARE @theshutdowndate datetime
    declare @theshutdownpcprofile int
    
    
    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 
    
    
    		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 
    			if  ((@theshutdownpcprofile=@theewitpcprofile) and (DATEDIFF(millisecond,@theshutdowndate,@theewitdate)<1))
    			
    			
    			print 'the shutdown pc profile is '+ convert(varchar(19), @theshutdownpcprofile, 121)  
    			print' the shutdown date is '+ convert(varchar(19), @theshutdowndate , 121) 
    
    
    					
    					
    			FETCH NEXT FROM shutdown_cursor INTO @theshutdownpcprofile,@theshutdowndate  
    
    			end
    			close shutdown_cursor 
    			deallocate shutdown_cursor 
    
    		
    FETCH NEXT FROM ewit_cursor INTO @theewitpcprofile,@theewitdate  
    
    end
    close ewit_cursor 
    deallocate ewit_cursor

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So, why not something like:
    Code:
    update tbl_pc_audit
    set shutdown_time = null
    where datediff (ms, shutdown_time, ewit-shutdown_time) > 20000
    As Wim said, you may be making this a whole lot more complex than it needs to be.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    There is still no need to resort to a cursor. Try this:
    Code:
    DELETE D
    FROM DaTable D
    	INNER JOIN DaTable T ON
    		DATEDIFF(millisecond, D.SHUTDOWN_TIME, T.EWIT_SHUTDOWN_TIME) < 20000
    WHERE D.SHUTDOWN_TIME IS NOT NULL
    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

  7. #7
    Join Date
    Feb 2005
    Posts
    30
    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.

    Code:
    select distinct d.SHUTDOWN_TIME
    FROM TBL_PC_AUDIT_SHUTDOWN D,TBL_PC_AUDIT_EWIT t
    	
    	
    	
    WHERE (d.pc_profile_id=t.pc_profile_id and DATEDIFF(millisecond,CONVERT( VARCHAR(24),T.EWIT_SHUTDOWN_TIME,113),CONVERT( VARCHAR(24),D.SHUTDOWN_TIME,113))<2000)

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, please try:
    Code:
    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.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have you considered to give my last code a try?

    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

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Pat,

    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

  11. #11
    Join Date
    Feb 2005
    Posts
    30
    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.


    Code:
    
    use CCCNew
    
    go
    
    DECLARE @theewitaudit int
    DECLARE @theewitdate datetime
    declare @theewitpcprofile int
    DECLARE @theaudit int
    DECLARE @theshutdowndate datetime
    declare @theshutdownpcprofile int
    DECLARE @SQLSDOWN VARCHAR
    
    
    DECLARE ewit_cursor CURSOR READ_ONLY FOR
    
    
    SELECT 
    PC_PROFILE_ID,
     EWIT_SHUTDOWN_TIME,
     AUDIT_ID
       
    FROM
       TBL_PC_AUDIT
       where EWIT_SHUTDOWN_TIME is not null
       
       
    
    OPEN ewit_cursor  
    FETCH NEXT FROM ewit_cursor INTO @theewitpcprofile,@theewitdate,@theewitaudit  
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN 
    
    
    		
    FETCH NEXT FROM ewit_cursor INTO @theewitpcprofile,@theewitdate,@theewitaudit 
    
    	INSERT INTO TBL_PC_AUDIT_EWIT VALUES (@theewitdate,@theewitpcprofile,@theewitaudit)
    			
    
    		
    end
    close ewit_cursor 
    deallocate ewit_cursor 
    
    
    DECLARE shutdown_cursor CURSOR READ_ONLY FOR
    		
    		SELECT 
    		PC_PROFILE_ID,
    		SHUTDOWN_TIME,
    		 AUDIT_ID
       
    		FROM
    		   TBL_PC_AUDIT
    		   where SHUTDOWN_TIME is not null
    
    	
    			OPEN shutdown_cursor  
    			FETCH NEXT FROM shutdown_cursor INTO @theshutdownpcprofile,@theshutdowndate,@theaudit  
    
    			WHILE @@FETCH_STATUS = 0  
    			BEGIN 
    			
    					
    					
    			FETCH NEXT FROM shutdown_cursor INTO @theshutdownpcprofile,@theshutdowndate,@theaudit
    			
    			INSERT INTO TBL_PC_AUDIT_SHUTDOWN VALUES (@theshutdowndate,@theshutdownpcprofile,@theaudit)
    			
    			
    
    			end
    			close shutdown_cursor 
    			deallocate shutdown_cursor 
    		
    			
    SELECT	distinct s.AUDIT_ID,
    	DateDiff(MILLISECOND, d.EWIT_SHUTDOWN_TIME,s.SHUTDOWN_TIME)
    FROM	TBL_PC_AUDIT_SHUTDOWN s
    JOIN	TBL_PC_AUDIT_EWIT d
    On	d.PC_PROFILE_id = s.PC_PROFILE_id
    where (DateDiff(MILLISECOND, d.EWIT_SHUTDOWN_TIME,s.SHUTDOWN_TIME)<40000)and (DateDiff(MILLISECOND, d.EWIT_SHUTDOWN_TIME,s.SHUTDOWN_TIME)>0)
    order by DateDiff(MILLISECOND, d.EWIT_SHUTDOWN_TIME,s.SHUTDOWN_TIME) 
    
    	
    			delete from tbl_pc_audit_shutdown
    			
    			delete from tbl_pc_audit_ewit

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2005
    Posts
    30
    Quote Originally Posted by blindman View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •