Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Max Record with a condition

    Hi guys,

    I am wondering how to select the Max date within a table where a timestamp is within 2 second of the other

    ID Timestamp
    1 NULL
    2 09/17/2012 11:04:32
    3 09/17/2012 11:05:44
    4 09/17/2012 11:05:45


    So I need a query to return

    ID Timestamp
    4 09/17/2012 11:05:45

    as 11:05:45 is the max of 09/17/2012 11:05:44 and 09/17/2012 11:05:45 and they are within 2 seconds of eachother.

    Thanks!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there were more data.

    For example:
    ID Timestamp
    1 NULL
    2 09/17/2012 11:04:32
    3 09/17/2012 11:05:44
    4 09/17/2012 11:05:45
    5 09/17/2012 11:05:46
    6 09/17/2012 11:05:47
    7 09/17/2012 11:05:50
    8 09/17/2012 11:05:52
    9 09/17/2012 11:05:53
    ...

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Thanks Tonkuma,

    There won't be any more within 10 seconds of the records. There are other fields in the table that give uniqueness but I want the post to be as simple as possible.

    More data could be

    ID Timestamp
    1 NULL
    2 09/17/2012 11:04:32
    3 09/17/2012 11:05:44
    4 09/17/2012 11:05:45
    5 09/17/2012 12:30:00
    6 09/17/2012 12:31:00

    and I would expect

    ID Timestamp
    4 09/17/2012 11:05:45
    6 09/17/2012 12:31:00

    returned

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There won't be any more
    What rules/rational are there?

    Why the following two records won't be?
    5 09/17/2012 11:05:46
    6 09/17/2012 11:05:47

    within 10 seconds of the records.
    What means within 10 seconds?
    Do you want to check data within every 10 seconds interval? Or, any other meaning?


    ID Timestamp
    1 NULL
    2 09/17/2012 11:04:32
    3 09/17/2012 11:05:44
    4 09/17/2012 11:05:45
    5 09/17/2012 12:30:00
    6 09/17/2012 12:31:00
    ... to select the Max date within a table where a timestamp is within 2 second of the other
    If a data was only one within 2 seconds, the record should take or not?

    (1) Yes
    Expected result might be
    ID Timestamp
    4 09/17/2012 11:05:45
    5 09/17/2012 12:30:00
    6 09/17/2012 12:31:00
    (2) No.
    Expected result might be
    ID Timestamp
    4 09/17/2012 11:05:45

  5. #5
    Join Date
    Sep 2011
    Posts
    44
    The database is being populated by an third party software by users.

    The 3rd part software enters in 2 entries (one after the other) and I need to know the second one which comes through within one second of the first (2 seconds is enough of a window to catch it)

    I need this second record for a trigger I am adding to the database.

    So there isnt any other situations of data other than me needing to return the second record.

    In writing this I think I may have a way to do it.

    When I read in the record in my trigger check to see if there is another record within 2 seconds before the first. If there is then the one I am looking at it the one I want.

    I think I will try and go down this route. If you have any other suggestions for a tidier query then all the better.

    THanks again

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The way to compare a new data(to be inserted) with old data of a table in a trigger
    is different from the way to compare two(or more) data of a table in a query.

  7. #7
    Join Date
    Sep 2011
    Posts
    44
    Thanks Tonkuma,

    I am aware of that, I thought it would have been easier to explain in the post as a query and then I could adjust..

    I got it working using the below code:

    entrylog is the timestamp

    GO
    /*05/16/2012 09:05:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --
    alter TRIGGER user.history_trigger ON user.history
    after INSERT,update
    AS

    declare @empref as varchar(10)
    declare @tablename as varchar(20)
    declare @fieldname as varchar (20)
    declare @newvalue as varchar(20)
    declare @changedate as datetime
    declare @maxchangedate as datetime
    declare @count as float
    declare @entrylog as datetime
    declare @homeroster as varchar(20)

    set @empref=(select empref from inserted)
    set @tablename=(select tablename from inserted)
    set @fieldname=(select fieldname from inserted)
    set @newvalue =(select newvalue from inserted)
    set @changedate =(select changedate from inserted)
    set @changedate =(select max(changedate) from user.history where tablename='TMSTMS' and fieldname='WP' and empref=@empref)
    set @homeroster = (select WPHOME from user.TMSEMP where empref=@empref )

    if @empref<>'' and @tablename='TMSTMS' and @fieldname='WP'
    begin
    update user.history
    set entrylog=getdate() where tablename='TMSTMS' and fieldname='WP' and empref=@empref and changedate=@changedate
    set @entrylog=getdate()

    set @count=(select count(*) from user.history
    where tablename='TMSTMS' and fieldname='WP' and empref=@empref and @entrylog>entrylog and entrylog>DATEADD(second, -2, @entrylog))

    if @count=1 and (@homeroster<>'' or @homeroster is not null)
    begin

    update user.history
    set newvalue = @homeroster
    where (changedate=@changedate and tablename='TMSTMS' and fieldname='WP' and empref=@empref)

    end


    end

Posting Permissions

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