You must Sign In to post a response.
  • Category: SQL Server

    Required Query to compare timestamps of two records

    Hi Team,

    I have a table with below columns and rows and I am expecting sequence of status for particular amount.
    if status "UM" comes before "RA" status then I should extract all that kind of records. Kindly give me the query.


    Payor amount status Timestamp

    25 198.08 RA 07/10/2014 15:37
    25 198.08 UM 07/10/2014 15:38

    45 456.9 UM 07/10/2014 15:39
    45 456.9 RA 07/10/2014 15:42


    Our query should display amount--> 456.9 record as UM received early than RA for this record.
  • #747336
    Hi,

    use the below SQL Query to compare time stamps of two records

    SELECT t1.*
    FROM table_12 t1
    JOIN (SELECT payor,
    MIN(timestamp) AS Timestamp
    FROM table_12
    GROUP BY payor) t2
    ON t1.payor = t2.payor
    AND t1.timestamp = t2.timestamp
    WHERE t1.status = 'UM'



    SELECT payor,
    amount,
    [status],
    [timestamp]
    FROM (SELECT *,
    ROW_NUMBER()
    OVER (
    partition BY payor
    ORDER BY timestamp ) AS [Row_Number]
    FROM table_12) AS TempTable
    WHERE [row_number] = 1
    AND [status] = 'UM'


  • Sign In to post your comments