plz help me in this query

Question

question

Ravindra 1 asked May 19, '10

plz help me in this query

i m sending u a table structure. and i want to execute a query on it. plz help me.

structure of table attendance log

CREATE TABLE [dbo].[AttendanceLog]( [TransID] [int] NOT NULL, [TransType] varchar NULL, [TransDtTime] [datetime] NULL, [EmpID] [int] NULL, [PCName] varchar)

Data in Table are INSERT INTO [Temp].[dbo].[AttendanceLog] ([TransID] ,[TransType] ,[TransDtTime] ,[EmpID] ,[PCName]) VALUES (101,1,15/5/2010 09:00:00 AM ,101,AAA) (102,1,15/5/2010 09:10:00 AM ,102,bbb) (103,1,15/5/2010 09:20:00 AM ,103,ccc) (104,0,15/5/2010 13:00:00 PM ,102,bbb) (105,0,15/5/2010 14:00:00 PM ,101,AAA) (106,1,15/5/2010 14:15:00 PM ,101,aaa) (107,0,15/5/2010 16:00:00 PM ,103,ccc) (108,0,15/5/2010 18:00:00 PM ,101,AAA) (109,1,15/5/2010 18:10:00 PM ,102,bbb)

now i want to execute a query which show report of single day like

[b]Empid LogInTime LogOutTime Duration [/b]

problem is that a employee can login and logout more than 1 time in a day.

i want a solution for that.

LogInTime and LogOutTime both calculated form transDtTime attribute on base of TransType("0" for logout and "1" for login) and Duration is (LogOutTime-LogInTime)

my query is

SELECT CONVERT(varchar(12), a.TransDtTime, 108) AS LoginTime, CONVERT(varchar(12), b.TransDtTime, 108) AS LogoutTime, CONVERT(varchar(12), b.TransDtTime - a.TransDtTime, 108) AS Duration FROM AttendanceLog AS a INNER JOIN AttendanceLog AS b ON a.EmpID = b.EmpID WHERE (a.TransType = '1') AND (b.TransType = '0')

thanks Ravindra Prajapat

sql-server-2005 query select

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-19T22:33:35Z

Squirrel 1 answered May 19, '10

select  li.EmpID,             
    LoginTime   = li.TransDtTime,            
    LogoutTime  = lo.TransDtTime,            
    duration = datediff(minute, li.TransDtTime, lo.TransDtTime)            
from    #AttendanceLog li            
    cross apply            
    (            
        select  TransDtTime = min(x.TransDtTime)            
        from    #AttendanceLog x            
        where   x.EmpID = li.EmpID            
        and x.TransType = 0            
        and x.TransDtTime   >= li.TransDtTime            
    ) lo             
where   li.TransType    = 1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question