SQL Query Group by issue

Question

I have a table with records like history table.

I have created following Query to get the desired result. However, it is not giving me final result that I want.

DECLARE @Customer_Id BIGINT=4
DECLARE @Month INT=2

SELECT 
    FH.UniqueFileId,FH.DocumentNo,MIN(FH.DateStamp) AS ReceivedTime,MAX(FH.DateStamp) SentTime
FROM 
    [dbo].[FileMovement_History] FH 
    INNER JOIN [dbo].[User_Master] UM ON UM.User_Id=FH.User_Id
WHERE 
    FH.UniqueFileId Is Not NULL AND FH.UniqueFileId!=''
    AND (@Customer_Id IS NULL OR UM.Customer_id = @Customer_Id)
    AND MONTH(FH.DateStamp)=@Month  
GROUP BY 
    FH.UniqueFileId,FH.DocumentNo
Order BY 
    FH.UniqueFileId

╔════════════════╦══════════════════════╦═════════════════════════════╦═════════════════════════════╗
║  UniqueFileId  ║      DocumentNo      ║        ReceivedTime         ║          SentTime           ║
╠════════════════╬══════════════════════╬═════════════════════════════╬═════════════════════════════╣
║ HS5536387_10HE ║ NULL                 ║ 2017-02-02 13:13:37.3451906 ║ 2017-02-02 13:19:19.0114047 ║
║ HS5536387_10HE ║ 120097583 / MC / CL7 ║ 2017-02-02 13:19:18.9801503 ║ 2017-02-02 13:19:19.1051605 ║
║ HS5536387_1HE  ║ NULL                 ║ 2017-02-02 13:13:34.6887572 ║ 2017-02-02 13:21:37.7405652 ║
║ HS5536387_1HE  ║ 3701341              ║ 2017-02-02 13:21:37.6936641 ║ 2017-02-02 13:21:37.8499434 ║
║ HS5536387_3HE  ║ NULL                 ║ 2017-02-02 13:13:35.5950873 ║ 2017-02-02 13:19:19.9958519 ║
╚════════════════╩══════════════════════╩═════════════════════════════╩═════════════════════════════╝

I want the following result where I need one entry for one UniqueFileId with minimum of datestamp and maximum of datestamp. I want extra column where is shows total timeduration between minimum and maximum date.

╔════════════════╦══════════════════════╦═════════════════════════════╦═════════════════════════════╗
║  UniqueFileId  ║      DocumentNo      ║        ReceivedTime         ║          SentTime           ║
╠════════════════╬══════════════════════╬═════════════════════════════╬═════════════════════════════╣
║ HS5536387_10HE ║ 120097583 / MC / CL7 ║ 2017-02-02 13:13:37.3451906 ║ 2017-02-02 13:19:19.0114047 ║
║ HS5536387_1HE  ║ 3701341              ║ 2017-02-02 13:13:34.6887572 ║ 2017-02-02 13:21:37.7405652 ║
║ HS5536387_3HE  ║ NULL                 ║ 2017-02-02 13:13:35.5950873 ║ 2017-02-02 13:19:19.9958519 ║
╚════════════════╩══════════════════════╩═════════════════════════════╩═════════════════════════════╝

Answer 1

Do a grouping only by the UniqueFileId field, and instead take the MIN(DocumentNo) value of this column:

SELECT FH.UniqueFileId,
       MIN(FH.DocumentNo) AS DocumentNo,
       MIN(FH.DateStamp)  AS ReceivedTime,
       MAX(FH.DateStamp)  AS SentTime
FROM [dbo].[FileMovement_History] FH 
INNER JOIN [dbo].[User_Master] UM
    ON UM.User_Id = FH.User_Id
WHERE COALESCE(FH.UniqueFileId, '') <> '' AND
      (@Customer_Id IS NULL OR UM.Customer_id = @Customer_Id) AND
      MONTH(FH.DateStamp) = @Month  
GROUP BY FH.UniqueFileId
ORDER BY FH.UniqueFileId

If you want to show the difference in time between the sent and received time in the format hh:mm:ss, then you can use the following expression:

CAST(DATEDIFF(HOUR,   MIN(FH.DocumentNo), MAX(FH.DocumentNo)) AS NVARCHAR(200)) + ":" +
CAST(DATEDIFF(MINUTE, MIN(FH.DocumentNo), MAX(FH.DocumentNo)) % 60 AS NVARCHAR(200)) +":" +
CAST(DATEDIFF(SECOND, MIN(FH.DocumentNo), MAX(FH.DocumentNo)) % 60 AS NVARCHAR(200))