October 2, 2011 at 2:00 am
This is my query.
Here , I have calculated InTime and OutTime .
Query:
=====
Select
(CASE(Cur.Status) WHEN 'OUT' THEN
(CASE(Prv.Status) WHEN 'IN' THEN
RIGHT(('0'+Convert(varchar,(sum(DateDiff(mi,Prv.Time,Cur.Time)/60)))),2)+':'
+RIGHT(('0'+Convert(varchar,(sum(DateDiff(mi,Prv.Time,Cur.Time)%60)))),2)
ELSE '' END)
ELSE '' END) AS InTime,
(CASE(Cur.Status) WHEN 'IN' THEN
(CASE(Prv.Status) WHEN 'OUT' THEN
RIGHT(('0'+Convert(varchar,(sum(DateDiff(mi,Prv.Time,Cur.Time)/60)))),2)+':'
+RIGHT(('0'+Convert(varchar,(sum(DateDiff(mi,Prv.Time,Cur.Time)%60)))),2)
ELSE '' END)
ELSE '' END) AS OutTime
from tblTime Cur LEFT OUTER JOIN tblTime Prv ON ((Cur.SNo=Prv.SNo+1) AND (Cur.EmpID = Prv.EmpID))
group by Cur.Status,Prv.Status,Prv.SNo ,Cur.SNo
Output:
======
InTime
01:25
00:33
01:11
00:19
02:07
01:05
00:38
OutTime
00:18
00:04
00:35
01:05
00:04
00:22
Now I need to sum Outime column. Pl Anybody can help how to sum Outtime column only.
October 2, 2011 at 4:33 am
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic1184035-391-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply