Maximum Date with Status

sth_syed · January 30, 2018, 6:23am

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

bitsmed · January 30, 2018, 8:24am

Something like:

Query

select top(1) with ties
       vin
      ,entry_date
      ,[status]
  from vs_transaction
 where status in ('RQT','Delete')
 order by rownumber() over(partition by vin
                               order by entry_date desc
                          )
;

harishgg1 · January 5, 2019, 10:04am

hi

i know this topic from long ago

i tried something different

if it helps great

Drop Create Data ...

  use tempdb 
go 

drop table vs_transaction
go 

create table vs_transaction
(
Vin varchar(100) null , 
entry_date datetime null , 
Status varchar(100) null 
)
go 

insert into vs_transaction select '3KPA351A1JE072740','2018-01-23 08:12:27.560','Delete'
insert into vs_transaction select '3KPA351A1JE072740','2018-01-22 15:32:01.000','RQT'
go 

select * from vs_transaction
go

SQL

;WITH cte 
     AS (SELECT vin, 
                Max(entry_date) AS Entry_Date1 
         FROM   vs_transaction 
         WHERE  status = 'RQT' 
                 OR status = 'Delete' 
         GROUP  BY vin) 
SELECT a.* 
FROM   vs_transaction a 
       JOIN cte b 
         ON a.vin = b.vin 
            AND a.entry_date = b.entry_date1

Results