x

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

more ▼

asked May 19, 2010 at 02:59 AM in Default

avatar image

Ravindra 1
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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            
more ▼

answered May 19, 2010 at 10:33 PM

avatar image

Squirrel 1
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2016
x425
x146

asked: May 19, 2010 at 02:59 AM

Seen: 1044 times

Last Updated: May 19, 2010 at 02:59 AM

Copyright 2016 Redgate Software. Privacy Policy