计算SQL Server 2014中的登录时间和注销时间 [英] calculating Login time and logout time in SQL Server 2014

问题描述

请帮我计算SQL Server 2014中的登录时间和注销时间。
 
请在下面的表中找到'LoginDetails'，其中
 isStatus 1表示登录时间和0表示退出时间
 
 
登录ID EmpID LogDetail isStatus 
 
 1 111 2015-09-18 09：29：21.637 1 
 2 111 2015-09-18 09：49：04.030 0 
 3 112 2015-09-18 10：31：52.247 1 
 4 112 2015-09-18 11：32：32.410 1 
 5 113 2015-09-18 11：32：32.410 1 
 6 113 2015-09-18 13：34：52.153 0 
 7 112 2015-09-18 13：50：52.153 0 
 
 
我需要以这种方式输出：
 
 PayrollID日期登录退出总时间
 111 09/18/2015 9:29 9:49 00:20:00 
 112 09/18/2015 10:31 
 113 09/18/2015 11:32 13:34 02:06:00 
 112 09/18/2015 11:32 13:50 02:22:00 

解决方案

如果总是有登录，那么或许类似

  SELECT  i。*，
（ SELECT  o.logdetail 
  FROM  LoginDetails o 
  WHERE  i.empid = o.empid 
  AND   CONVERT （ date ，i.logdetail）=  CONVERT （ date ，o.logdetail）
  AND  o.logdetail> i.logdetail 
  AND  o.isstatus =  0  
  AND  o.logdetail =（ SELECT  MIN（o2.logdetail）
  FROM  LoginDetails o2 
  WHERE  o2.logdetail> i.logdetail）
） AS 注销
  FROM  LoginDetails i 
  WHERE  i.isstatus =  1  

附加：



添加总时间（分钟）。总时间的格式应在客户端完成

  SELECT  a.loginid，
 a.empid，
 a.logintime，
 a.logouttime，
 DATEDIFF（MINUTE，a.logintime，a.logouttime）
  FROM （
  SELECT  i.loginid，
 i.empid，
 i.logdetail  as  logintime，
（ SELECT  o.logdetail 
  FROM  LoginDetails o 
  WHERE  i.empid = o.empid 
  AND   CONVERT （ date ，i.logdetail）=  CONVERT （ date ，o.logdetail）
  AND  o.logdetail> i.logdetail 
  AND  o.isstatus =  0  
  AND  o.logdetail =（ SELECT  MIN（o2.logdetail）
  FROM  LoginDetails o2 
  WHERE  o2.logdetail> i.logdetail）
） AS  logouttime 
  FROM  LoginDetails i 
  WHERE  i.isstatus =  1 ）a 

Please help me in calculating login time and logout time in SQL Server 2014.

Please find the below table with 'LoginDetails' where in
isStatus 1 indicates Login time and 0 indicates Logout time


LoginID    EmpID     LogDetail                    isStatus

1	    111	     2015-09-18 09:29:21.637	       1
2	    111	     2015-09-18 09:49:04.030	       0
3	    112	     2015-09-18 10:31:52.247	       1
4	    112	     2015-09-18 11:32:32.410	       1
5	    113	     2015-09-18 11:32:32.410	       1
6	    113	     2015-09-18 13:34:52.153	       0
7	    112	     2015-09-18 13:50:52.153	       0


I need the output in this way:

PayrollID	Date	Login	Logout  Totaltime
111	    09/18/2015	9:29	9:49    00:20:00
112	    09/18/2015	10:31
113	    09/18/2015	11:32	13:34   02:06:00
112	    09/18/2015	11:32	13:50   02:22:00

解决方案

If there's always a login then perhaps something like

SELECT i.*,
       (SELECT o.logdetail
	FROM LoginDetails o
        WHERE i.empid = o.empid 
        AND CONVERT(date, i.logdetail) = CONVERT(date, o.logdetail)
        AND o.logdetail > i.logdetail
        AND o.isstatus = 0
        AND o.logdetail = (SELECT MIN(o2.logdetail) 
                           FROM LoginDetails  o2
                           WHERE o2.logdetail > i.logdetail)
	   ) AS logout
FROM LoginDetails i
WHERE i.isstatus = 1

ADDITION:

Added total time in minutes. The formatting for the total time should be done at client side

SELECT a.loginid,
       a.empid,
       a.logintime,
       a.logouttime,
       DATEDIFF(MINUTE, a.logintime, a.logouttime)
FROM (
       SELECT i.loginid,
              i.empid,
              i.logdetail as logintime,
             (SELECT o.logdetail
              FROM LoginDetails o
              WHERE i.empid = o.empid
              AND CONVERT(date, i.logdetail) = CONVERT(date, o.logdetail)
              AND o.logdetail > i.logdetail
              AND o.isstatus = 0
              AND o.logdetail = (SELECT MIN(o2.logdetail)
                                 FROM LoginDetails  o2
                                 WHERE o2.logdetail > i.logdetail)
             ) AS logouttime
   FROM LoginDetails i
   WHERE i.isstatus = 1) a

这篇关于计算SQL Server 2014中的登录时间和注销时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！