February 20, 2011 at 11:30 pm
Hi I am having table attendance and i am using the query as
SELECT ID, LOGINTIME, LOGOUTTIME,CONVERT(VARCHAR(8), (LOGOUTtime - LOGINtime), 108) as 'Total Time' from ATTENDSHEET where LOGINTIME between '2011-02-19' and '2011-02-20'
group by ID, LOGINTIME, LOGOUTTIME which gives me the result on each login and logout, i need the query to take first login time and last logout time and give me totals as hours:mins:ss, i have attached the screenshot on the same, kindly help
February 21, 2011 at 1:07 am
Try this:
DECLARE @table TABLE
(
ID VARCHAR(30),
LogInTime DATETIME ,
LogOutTime DATETIME
)
INSERT INTO @table
SELECT 'ISHEK' , '2011-02-19 07:01:54.000' ,'2011-02-19 07:52:54.000'
UNION ALL SELECT 'ISHEK' , '2011-02-19 07:53:54.000' ,'2011-02-19 07:54:54.000'
UNION ALL SELECT 'ISHEK' , '2011-02-19 07:55:54.000' ,'2011-02-19 07:56:54.000'
UNION ALL SELECT 'ISHEK' , '2011-02-19 08:01:54.000' ,'2011-02-19 14:52:54.000'
; WITH Ranked_Logins_CTE AS
(
SELECT ID,
LogInTime ,
LogOutTime,
RN_Login = ROW_NUMBER() OVER(PARTITION BY ID, DATEPART(DD,LogInTime) ORDER BY LogInTime),
RN_LogOut = ROW_NUMBER() OVER(PARTITION BY ID, DATEPART(DD,LogOutTime) ORDER BY LogOutTime DESC)
FROM @table
)
SELECT ID ,
First_Login = MIN (LogInTime) ,
Last_LogPut = MAX (LogOutTime) ,
Time_In_Office_InSeconds = DATEDIFF ( ss , MIN (LogInTime) , MAX (LogOutTime) )
FROM Ranked_Logins_CTE
GROUP BY ID,DATEPART(DD,LogInTime)
February 21, 2011 at 3:56 am
or this?
SELECT ID ,
First_Login = MIN (LogInTime) ,
Last_LogPut = MAX (LogOutTime) ,
Time_In_Office_InSeconds = DATEDIFF ( ss , MIN (LogInTime) , MAX (LogOutTime) )
FROM @table
GROUP BY ID , DATEPART(DD,LogInTime)
February 21, 2011 at 4:36 am
Hi Thank you very much, it worked well 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply