Q:

Get the MAX datetime value from the MIN date

My table has a part number, part serial number, part test number, part test result and date part tested. The keys are part number, part serial number, part test number and date part tested. A part is tested with the same serial number and the test number in different days and different time in same day. I want to see the first day it was tested and last result of that day. I want to create a view. There can be different part number and each part number can have different serial numbers. each part number is associated with different test numbers
Below is the results for a particular part number, serial #, test

part number serial number   TEST #       DATE   test-value
555-99          abcd123         10  11/30/18 2:02   0
555-99          abcd123         10  11/30/18 2:22   13714.66797
555-99          abcd123         10  11/30/18 2:23   2
555-99          abcd123         10  11/30/18 9:22   5
555-99          abcd123         10  11/30/18 10:22  14809.70703
555-99          abcd123         10  1/9/19 6:13 14574.62891
555-99          abcd123         10  1/9/19 6:14 14084.62891
555-99          abcd123         10  1/9/19 14:53    14119.66797
555-99          abcd123         10  1/9/19 14:54    13874.72656
555-99          abcd123         10  1/9/19 14:53    14844.74609
555-99          abcd123         10  1/11/19 7:19    15404.76563
555-99          abcd123         10  1/15/19 17:47   14179.76563
555-99          abcd123         10  1/17/19 0:17    14214.64844
555-99          abcd123         10  1/17/19 0:17    14216.64944

Output should be 
555-99  abcd123 10  11/30/18 9:22   5

I want to create a view that has all part numbers with each serial number and their test number with last time when it was first tested.
Any help is greatly appreciated.
This give the desired result if i pass the serial number:

 DECLARE @DATE DATETIME2(7);
   DECLARE @TOPDATE DATETIME2(7);
   DECLARE @MAXDATE DATETIME2(7);
    SELECT @Date = MIN(DATA_Date_Time) FROM DATA_Fields WHERE DATA_Serial = 
    'UNLDR598' ;
    SELECT @TOPDATE   = dateadd(hh,  23,  @DATE)
    select  @MAXDATE  =max(DATA_Date_Time )from DATA_Fields where  
    DATA_Date_Time    >   @DATE AND     DATA_Date_Time    <  @TOPDATE  AND 
    DATA_Serial = 'UNLDR598'  
     SELECT  DATA_PART,DATA_SERIAL, DATA_TEST,  data_value ,DATA_Date_Time 
     FROM DATA_Fields
  
       where DATA_Serial = 'UNLDR598' 
       AND   DATA_Date_Time = @MAXDATE 


 <br> 

A:

Here is a script, which will do the trick. It is not so elegant, but should work faster.
SELECT yt.*
FROM #YourTable AS yt
OUTER APPLY (
    SELECT md = MIN(CAST(i.[DATE] AS date)) FROM #YourTable AS i
    WHERE i.[part number] = yt.[part number]
        AND i.[serial number] = yt.[serial number]
        AND i.[TEST #] = yt.[TEST #]
) AS MinDate
OUTER APPLY (
    SELECT MT = MAX(i.[DATE]) FROM #YourTable AS i
    WHERE i.[part number] = yt.[part number]
        AND i.[serial number] = yt.[serial number]
        AND i.[TEST #] = yt.[TEST #]
        AND CAST(i.[DATE] AS date) = MinDate.MD
) AS MaxTime
WHERE yt.DATE = MaxTime.MT

