无法找出准确的查询来获取我的报告的时间戳 [英] Unable to figure out accurate query to get timestamps for my report

问题描述

我正在尝试根据登录会话的用户数据编写报告.

I am trying to write a report based on data of users logging into a session.

我需要能够获得从第一个人加入会议到最后一个人离开的完整会议时间.

I need to be able to get a full session time from when the first person joins the meeting to when the last person leaves.

• 当有人加入会议时，它会被记录为初始化-加载视频聊天窗口"
• 有两种方式可以结束会议，但一种方式是被记录.- 有一个结束聊天"；用户可以使用并记录为视频聊天-结束聊天"的按钮.- 如果用户不使用该按钮而只是退出程序/浏览器，则数据库不会记录该情况，我想使用 logType 列中最后记录的元素.

我希望它看起来像下面这样:

I would like it to look like this below:

这是我的查询:

select vl.originalChatSessionID, 
       CONVERT(DATE, min(vl.ReceivedDateTime)) as VideoDate, 
       --CONVERT(TIME, min(vl.ReceivedDateTime)) as StartTime,
       min(vl.ReceivedDateTime) as StartTime2,
       --CONVERT(TIME, max(vl.ReceivedDateTime)) as EndTime,
       max(vl.ReceivedDateTime) as EndTime2,
       DATEDIFF(MINUTE, min(vl.ReceivedDateTime), max(vl.ReceivedDateTime)) as SessionLength
from iclickphrDxvideolog vl
    --inner join iclickphrDxVideoHistory vh
    --  on vl.originalChatSessionID = vh.meetingid
    --  and vl.applicationUserID = vh.applicationUserID
where originalChatSessionID = @MeetingSessionID
    --and (vl.logType = 'Initialize-Load Video chat Window' or vl.logType = 'Video Chat-End Chat')
group by originalChatSessionID

问题是我正在获取 logType 列中第一个记录的元素和最后一个记录的元素，我知道这一点.如果我取消注释 where 子句中它所说的部分；--and (vl.logType = 'Initialize-Load Video chat Window' or vl.logType = 'Video Chat-End Chat')，那么我对会话的开始时间没有问题....但是有会话结束时间的一个大问题.

Problem is I am grabbing the first logged element and last logged element in the logType column, and I know that. If i uncomment the part in the where clause where it says; --and (vl.logType = 'Initialize-Load Video chat Window' or vl.logType = 'Video Chat-End Chat'), then i do not have an issue with the Start time of the session....but have a big issue with the End Time of the session.

下面是原始数据的样子:

Below is a picture of how the raw data looks:

推荐答案

我假设 originalChatSessionID 定义了一个唯一的会话.如果不是，那么您将必须更改PARTITION BY"子句以镜像使其唯一的一个或多个列.

I've a assumed that originalChatSessionID defines a unique session. If not then you will have to change the 'PARTITION BY' clause to mirror the column or columns that make it unique.

这也假设 ReceivedDateTime 是 datetime 数据类型

This also assumes that ReceivedDateTime is a datetime datatype

SELECT DISTINCT 
       vl.originalChatSessionID, 
       VideoDate = MIN(vl.ReceivedDateTime) OVER(PARTITION BY originalChatSessionID), 
       StartTime = MIN(ISNULL(sc.StartChat, vl.ReceivedDateTime)) OVER(PARTITION BY originalChatSessionID),
       EndTime = MAX(ISNULL(ec.EndChat, vl.ReceivedDateTime)) OVER(PARTITION BY originalChatSessionID),
       SessionLength = DATEDIFF(
                minute,
                MIN(ISNULL(sc.StartChat, vl.ReceivedDateTime)) OVER(PARTITION BY originalChatSessionID), 
                MAX(ISNULL(ec.EndChat, vl.ReceivedDateTime)) OVER(PARTITION BY originalChatSessionID)
                )
    FROM iclickphrDxvideolog vl
        LEFT JOIN (
                    SELECT originalChatSessionID, StartChat = MIN(ReceivedDateTime) 
                        WHERE logType = 'Initialize-Load Video chat Window'
                        GROUP BY originalChatSessionID
                    ) sc ON vl.originalChatSessionID = sc.originalChatSessionID
        LEFT JOIN (
                    SELECT originalChatSessionID, EndChat = MAX(ReceivedDateTime) 
                        WHERE logType = 'Video Chat-End Chat'
                        GROUP BY originalChatSessionID
                    ) ec ON vl.originalChatSessionID = ec.originalChatSessionID

这是未经测试的，因为我没有时间重新创建您的数据集.如果您需要更多帮助，我建议您发布一个脚本来重新创建您的示例数据，以便人们可以使用它进行测试.

This is untested as I don't have time to recreate your dataset. If you need more help I suggest you post a script to recreate your sample data so people can use it to test against.

上面使用了两个子查询，一个获取Initialize-Load Video chat Window的第一个实例，一个获取Video Chat-End Chat的最后一个实例.我对这些进行了 LEFT JOIN 编辑，因此它们将返回 NULL 值，但未找到任何内容.在查询的主要部分，我使用 ISNULL() 来测试是否未找到开始记录，然后使用会话的最早记录，如果未找到结束记录，则使用会话的最后一条记录.

The above uses two sub queries, one to get the first instance of Initialize-Load Video chat Window and one to get the last instance of Video Chat-End Chat. I have LEFT JOINed to these so they will return NULL values is nothing is found. In the main part of the query, I've used ISNULL() to test if the start record is not found then use the earliest record for the session and if the end record is not found then use the last record for the session.

请注意，没有分组，但我使用 DISTINCT 来获得类似的结果.此 SQL 语句没有 WHERE 子句，因此您可以从中创建一个视图，然后简单地将该视图与 WHERE 一起使用报告条款.

Note that there is no grouping but I have used DISTINCT to get a similar result. This SQL statement does not have a WHERE clause so you could create a view from it then simply use that view with a WHERE clause for your report.

这篇关于无法找出准确的查询来获取我的报告的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！