dimanche 19 juin 2016

How to get latest records based on Batch ID

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