calculating Login time and logout time in SQL Server 2014

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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

Posted 17-Sep-15 18:42pm

Mohammed_Faisal_Majeed

Updated 17-Sep-15 18:55pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-09-17T19:09:00

If there's always a login then perhaps something like

SQL

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

SQL

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