Post reply

Maximum Date with Status

  • January 29, 2018 at 11:23 pm

    #410112

    I have table where i have Status (Delete and RQT) with Entry_Date (DateTime) and Vin number.

    3KPA351A1JE072740 - 2018-01-23 08:12:27.560 - Delete
    3KPA351A1JE072740 - 2018-01-22 15:32:01.000 - RQT

    In the above table i need ony one record with Vin,Entry_Date, status either (Delete or RQT) with maximum Entry_Date Value i get the above result with this query.

    Select Vin,Max(Entry_Date) as Entry_Date1,Status
    from VS_Transaction where Status='RQT' or Status='Delete'
    group by Vin,Status order by Vin

  • January 29, 2018 at 11:32 pm

    #1977796

    Join to a subquery to find the row you need. You can also use a CTE with the ROW_NUMBER function.

    Select a.Vin,aEntry_Date as Entry_Date1,a.Status
    from VS_Transaction a
    inner join (Select Vin,Max(Entry_Date) as Entry_Date
    from VS_Transaction where Status='RQT' or Status='Delete'
    group by Vin) AS b ON a.Vin = b.Vin AND a.Entry_Date = b.Entry_Date
    where a.Status='RQT' or a.Status='Delete'
    order by a.Vin

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply