plz help me in this query

Question

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

Answer 1

0

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

answered May 19 '10 at 10:33 PM

Squirrel 1
1.6k ● 1 ● 3

hi dear i m using sql server 2005 cros apply is not supported there i try cross join but it is not working

May 20 '10 at 09:50 PM Ravindra 1

SELECT li.EmpID, li.TransDtTime AS LoginTime, lo.TransDtTime AS LogoutTime, DATEDIFF(minute, li.TransDtTime, lo.TransDtTime) AS duration
FROM AttendanceLog AS li CROSS JOIN
(SELECT MIN(TransDtTime) AS TransDtTime
FROM AttendanceLog AS x
WHERE (EmpID = li.EmpID) AND (TransType = 0) AND (TransDtTime >= li.TransDtTime)) AS lo
WHERE (li.TransType = 1)

i m using this query but it is not working

May 20 '10 at 09:53 PM Ravindra 1

Ravindra, CROSS APPLY is supported in SQL Server 2005.

May 23 '10 at 01:08 PM Squirrel 1

(comments are locked)

plz help me in this query

Follow this question

Related Questions