Trying to get Start Time and End Time of a meeting in a Table

Question

I need to get a Start Time and End time of a video chat meeting, depending on when the video started and ended. Meaning I would like to get the Start time based on the first initialized load chat window, and the End Time based on the last video end chat, all by the same user ID. Also would need to extract the date from the Times, so i can get a date of the meeting. Below is my code and data coming out. Hoping someone can provide more help.

So i would like to get one row that shows: Video Date, Start Time, End Time, and Session Length.

select distinct originalChatSessionID,
    logID,
    logType,
    logDetail,
    applicationUserID,
    --videoDate,
    min(receivedDateTime) as 'Start Time',
    max(receivedDateTime) as 'End Time'



  FROM iclickphrDxvideolog 
    --inner join iclickphrDxvideo v
    --  on l.originalChatSessionID = v.MeetingSessionID

  where originalChatSessionID = @MeetingSessionID
    and (logType = 'Initialize-Load Video chat Window' or logType = 'Video Chat-End Chat')
  group by originalChatSessionID, /*videoDate,*/receivedDateTime, logID, logType, logDetail, applicationUserID

Answer 1

To get one row that shows: Video Date, Start Time, End Time, and Session Length, this should do it:

select originalChatSessionID, 
       CONVERT(DATE, min(ReceivedDateTime)) as VideoDate, 
       CONVERT(TIME, min(ReceivedDateTime)) as StartTime,
       CONVERT(TIME, max(ReceivedDateTime)) as EndTime,
       DATEDIFF(MINUTE, min(ReceivedDateTime), max(ReceivedDateTime)) as SessionLength
from iclickphrDxvideolog
where originalChatSessionID = @MeetingSessionID
group by originalChatSessionID

I've assumed your SQL dialect is SQL Server and that you are happy to show the session length in minutes :-)