Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: Select most recent record

    Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.

    Normally 1 record is added to the db.
    00:00:00
    01:00:00
    02:00:00
    ...
    21:00:00
    22:00:00
    23:00:00

    All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.

    I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01

    We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement

    This is what we have know. It used to work fine untill we had 2 record being added instead of one.
    Code:
    SELECT     TimeOfCapture, Value1, Value2, Value3
    FROM         FactoryTable
    WHERE     (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))

  2. #2
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    I understand that you want to delete the records that you describe from the FactoryTable. Here's what you are looking for ... I guess:
    First show all records, their first next record and the time diffence between the two exprssed in hours:

    Code:
    select  A.TimeOfCapture
    ,       (select  min(B.TimeOfCapture) from FactoryTable B where B.TimeOfCapture > A.TimeOfCapture )
    ,       datediff(hh,A.TimeOfCapture, (select  min(B.TimeOfCapture) from FactoryTable B where B.TimeOfCapture > A.TimeOfCapture))
    from    FactoryTable A
    If that is satisfying, delete all records that are indicated '0' in the third column of the previous select by:

    Code:
    begin tran
    
    delete  FactoryTable
    from    FactoryTable A
    where   datediff(hh,A.TimeOfCapture, (select  min(B.TimeOfCapture) from FactoryTable B where B.TimeOfCapture > A.TimeOfCapture)) = 0
    
    -- commit
    Check the results before doing the actual commit !!!

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


Posting Permissions

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