In the following table, I need to find out the login date and how long a user's session spanned. For example, empid=1 logged in (typeid=1) on 1/1/2009 @ 3:20 and logged off (typeid=2) the same day at 3:25. The output should be:
在下表中,我需要找出登录日期以及用户会话的跨越时间。例如,empid = 1在1/1/2009 @ 3:20登录(typeid = 1)并在同一天3:25注销(typeid = 2)。输出应该是:
logged on, session length, sessionid
1/1/2009 3:20 5:00 014A3B2C-3874-4D84-A5B3-F234AA2AE30D
Each sessionid is unique. How do I create such a query?
每个sessionid都是唯一的。如何创建这样的查询?
create table a (empid int, typeid int, created datetime, sessionid nvarchar(36))
insert into a(empid, typeid, created, sessionid)
values(1, 1, '01/01/2009 3:20:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30D')
insert into a(empid, typeid, created, sessionid)
values(1, 2, '01/01/2009 3:25:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30D')
insert into a(empid, typeid, created, sessionid)
values(2, 1, '01/09/2009 4:20:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30E')
insert into a(empid, typeid, created, sessionid)
values(3, 1, '01/10/2009 3:25:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30F')
insert into a(empid, typeid, created, sessionid)
values(2, 2, '01/09/2009 4:23:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30E')
insert into a(empid, typeid, created, sessionid)
values(3, 2, '01/10/2009 4:25:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30F')
select * from a
drop table a
-- EDIT -- There are some scenarios I'd like to filter out. As an example, for empid=1, it could be this instead:
- 编辑 - 有些情况我想过滤掉。例如,对于empid = 1,它可能是这样的:
insert into a(empid, typeid, created, sessionid)
values(1, 1, '01/01/2009 3:20:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30D')
insert into a(empid, typeid, created, sessionid)
values(1, 1, '01/01/2009 3:25:00', '014A3B2C-3874-4D84-A5B3-F234AA2AE30D')
The above empid doesn't have a matching typeid=2. There are two typeid=1.
上面的empid没有匹配的typeid = 2。有两个typeid = 1。
3 个解决方案
#1
1
select empid,sessionid,
min(created),max(created),
datediff(mi,min(created),max(created))
from a
group by empid,sessionid
#2
1
SELECT LogOn.[created] as [logged on], DATEDIFF(day,LogOn.[created],LogOff.[created]) + ' ' + DATEDIFF(hour,LogOn.[created],LogOff.[created]) + ':' + DATEDIFF(minutes,LogOn.[created],LogOff.[created]) + ':' + DATEDIFF(second,LogOn.[created],LogOff.[created]), a.[sessionid]
FROM a as [LogOn]
JOIN a as [LogOff] ON LogOn.[sessionid] = LogOff.[sessionid]
WHERE LogOn.[typeid] = 1 AND LogOff.[typeid] = 2
#3
1
SELECT SessionId,
MIN(CASE WHEN typeid = 1 THEN created END) [logged on],
DATEDIFF(SECOND,MIN(CASE WHEN typeid = 1 THEN created END),MIN(CASE WHEN typeid = 2 THEN created END)) [session length]
FROM a
GROUP BY sessionId
You will need to manipulate the session length to obtain the desired output format, though.
但是,您需要操纵会话长度以获得所需的输出格式。
#1
1
select empid,sessionid,
min(created),max(created),
datediff(mi,min(created),max(created))
from a
group by empid,sessionid
#2
1
SELECT LogOn.[created] as [logged on], DATEDIFF(day,LogOn.[created],LogOff.[created]) + ' ' + DATEDIFF(hour,LogOn.[created],LogOff.[created]) + ':' + DATEDIFF(minutes,LogOn.[created],LogOff.[created]) + ':' + DATEDIFF(second,LogOn.[created],LogOff.[created]), a.[sessionid]
FROM a as [LogOn]
JOIN a as [LogOff] ON LogOn.[sessionid] = LogOff.[sessionid]
WHERE LogOn.[typeid] = 1 AND LogOff.[typeid] = 2
#3
1
SELECT SessionId,
MIN(CASE WHEN typeid = 1 THEN created END) [logged on],
DATEDIFF(SECOND,MIN(CASE WHEN typeid = 1 THEN created END),MIN(CASE WHEN typeid = 2 THEN created END)) [session length]
FROM a
GROUP BY sessionId
You will need to manipulate the session length to obtain the desired output format, though.
但是,您需要操纵会话长度以获得所需的输出格式。