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')

Ravindra Prajapat

more ▼

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

Ravindra 1 gravatar 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

Squirrel 1 gravatar image

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, 2010 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, 2010 at 09:53 PM Ravindra 1
Ravindra, CROSS APPLY is supported in SQL Server 2005.
May 23, 2010 at 01:08 PM Squirrel 1
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: May 19, 2010 at 02:59 AM

Seen: 915 times

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