October 1, 2011 at 3:44 am
Hi friends,
my Table name : Tbldifference
My requirement is :
1.Find difference between first IN and OUT ie.(first and last occurencetime diff)
2.Then find diff between each IN and OUT except first IN and OUT.
This is my table record.
--------------------------
sno cardno name Empid Email Occurrencetime Accessdate Swipetimings Swiptestatus
=======================================================================================================
1101george211George@yahoo.com2011-10-01 09:26:40.8272011-10-01 00:00:00.00009:26 IN
2101george211George@yahoo.com2011-10-01 10:51:40.8272011-10-01 00:00:00.00010:51 OUT
3101george211George@yahoo.com2011-10-01 11:09:40.8272011-10-01 00:00:00.00011:09 IN
4101george211George@yahoo.com2011-10-01 11:42:40.8272011-10-01 00:00:00.00011:42 OUT
5101george211George@yahoo.com2011-10-01 11:46:32.8272011-10-01 00:00:00.00011:46 IN
6101george211George@yahoo.com2011-10-01 12:57:32.8272011-10-01 00:00:00.00012:57 OUT
October 1, 2011 at 7:12 am
To help those who want to help you with tested code, please click on the first link in my signature block, read the article, and use the sample T-SQL code to post table definition and to insert sample data. Your modified post would then look something like:
CREATE TABLE #T(sno INT,cardno INT,Name VARCHAR(10),Empid INT,Email VARCHAR(20),Occurrencetime DATETIME,
Accessdate DATETIME, Swipetimings DATE, Swiptestatus VARCHAR(3))
INSERT INTO #T
SELECT 1, 101,'george',211,'George@yahoo.com','2011-10-01 09:26:40.827','2011-10-01 00:00:00.000','09:26','IN'
October 2, 2011 at 1:54 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 OutTime
00:18
00:04
00:35
01:05
00:04
00:22
01:25
00:33
01:11
00:19
02:07
01:05
00:38
Now I need to sum Outime column. Pl Anybody can help how to sum Outtime column only.
October 2, 2011 at 1:57 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:
======
OutTime
00:18
00:04
00:35
01:05
00:04
00:22
InTime
01:25
00:33
01:11
00:19
02:07
01:05
00:38
October 2, 2011 at 1:25 pm
I'm really not sure what you are actually looking for as output.
It would really help if you would post the DDL for the tables, sample data for those tables, and the expected results base on the sample data. The article BitBucket asked you to read will show you how to post this information.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply