declare @tab table
(
BatchID INT,
Code VARCHAR(20),
CommType INT,
LastStatus VARCHAR(5),
SourceModiifedLastDate varchar(30)
)
INSERT INTO @tab(BatchID, Code, CommType, LastStatus, SourceModiifedLastDate)
VALUES (1, 'A003-3', 3, 'I', '2013-06-17 21:28:01.827'),
(2, 'A004-1', 1, 'I', '2014-06-17 21:28:01.827'),
(6, 'A003-3', 3, 'U', '2015-06-17 21:28:01.827'),
(9, 'A003-3', 3, 'D', '2015-06-17 21:28:01.827'),
(11, 'A004-1', 3, 'D', '2013-06-17 21:28:01.827'),
(12, 'A004-1', 1, 'I', '2015-06-17 21:28:01.827'),
(16, 'A005-3', 3, 'I', '2011-06-17 21:28:01.827'),
(19, 'A005-3', 3, 'D', '2013-0617 21:28:01.827'),
(20, 'A006-3', 3, 'U', '2011-06-17 21:28:01.827'),
(21, 'A006-3', 3, 'I', '2013-0617 21:28:01.827')
Select * from @tab
Here in my sample data I need to get only Laststatus = 'D' records based on latest BatchID.
For example if you see Code = 'A003-3' it got inserted, updated and deleted I need to get this record
If you see code = 'A004-1' it got inserted, deleted and inserted I don't need this record.
Output should be :
BatchID Code CommType LastStatus SourceModiifedLastDate
---------------------------------------------------------------
9 A003-3 3 D 2015-06-17 21:28:01.827
19 A005-3 3 D 2013-06-17 21:28:01.827
I need to get only latest deleted records based on latest BatchID
and latest date.
I have tried using MAX
condition and GROUP BY
to filter records but I'm unable to get what I'm looking for.
Please help me find a solution
Aucun commentaire:
Enregistrer un commentaire