# Logout and Login Time difference very difficult to find

• ### Question

• Dear All,
I am having a table call trans where all the swipe transaction are recorded bay wise.I am  looking for a query where I am able to get the time Difference when user get out from bay and come in their bay, so I need to calculate the time diffrence between bayout and next bayin
Please see the sample data below

 Date Time EmpID CID Gate InOut Break 29/05/2012 19:32 M0728 01 Recption Door In 29/05/2012 19:33 M0728 02 Bay 1 In 29/05/2012 20:45 M0728 02 Bay 1 out 27 29/05/2012 21:01 M0728 01 Recption Door out 29/05/2012 21:11 M0728 01 Recption Door In 29/05/2012 21:12 M0728 02 Bay 1 In 29/05/2012 21:13 M0728 02 Bay 1 out 29/05/2012 21:13 M0728 02 Bay 1 In 29/05/2012 23:01 M0728 01 Recption Door out 11 29/05/2012 23:12 M0728 01 Recption Door In

if you see the data in Bold user M0728 get out from bay 1 at 20.45  and get in at 21.12 so the time difference between these two is 27 min. logic should be like to calulate the time of logout from bay and login to bay.

for last 2 days i am working on same.

Regards,

Vipin jha

Thankx & regards, Vipin jha MCP

Tuesday, July 3, 2012 8:27 AM

• Assume that one out must have a corresponding in, this is a pair. Or otherwise it will be a permanent leave.

Also, from your explanation, I understand that you only concern leaving Bays not the reception door.

Not a simple solution, try this:

```Declare @MyTable Table ([Date] Date, Time Time(0), EmpID varchar(10), CID int, Gate varchar(20), InOut varchar(10))

Insert into @MyTable
Select '2012-05-29', '19:32:00', 'M0728', 1, 'Recption Door', 'In' Union All
Select '2012-05-29', '19:33:00', 'M0728', 2, 'Bay 1', 'In' Union All
Select '2012-05-29', '20:45:00', 'M0728', 2, 'Bay 1', 'out' Union All
Select '2012-05-29', '21:01:00', 'M0728', 1, 'Recption Door', 'out' Union All
Select '2012-05-29', '21:11:00', 'M0728', 1, 'Recption Door', 'In' Union All
Select '2012-05-29', '21:12:00', 'M0728', 2, 'Bay 1', 'In' Union All
Select '2012-05-29', '21:13:00', 'M0728', 2, 'Bay 1', 'out' Union All
Select '2012-05-29', '21:13:00', 'M0728', 2, 'Bay 1', 'In' Union All
Select '2012-05-29', '23:01:00', 'M0728', 1, 'Recption Door', 'out' Union All
Select '2012-05-29', '23:12:00', 'M0728', 1, 'Recption Door', 'In'

Select	A.[Date], A.[Time], A.EmpID, A.CID, A.Gate, A.MyTimeOut, B.MyTimeIn
, DATEDIFF(mi, A.MyTimeOut, B.MyTimeIn) As BreakTime
From
(
Select	[Date], [Time], Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120) As MyTimeOut, EmpID, CID, Gate, InOut AS MyOut
From	@MyTable
Where	Gate Like 'Bay%'
And InOut = 'Out'
) A
Outer Apply
(
Select	Top 1 Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120) As MyTimeIn
From	@MyTable C
Where	A.EmpID = C.EmpID
And A.CID = C.CID
And A.Gate = C.Gate
And C.InOut = 'In'
And A.MyTimeOut <= Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)
Order by 	Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)
) B```

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Tuesday, July 3, 2012 10:19 AM
• YOu can create a persisted computed column on your table, like  Mydatetime = Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)

After that you can create a non clustered index to boost your query perfromance. Like below:

create nonclustered index idx_your_index_Name on Your_Table( Empid, gate, inout, mydatetime) include ([DAte], [Time]);

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

• Marked as answer by Wednesday, July 4, 2012 4:58 AM
Tuesday, July 3, 2012 12:12 PM

### All replies

• Select Empid, gate, Min(time) EntryTime, Max(time) ExitTime, datediff(mi, Min(time),Max(time)) timediff
from <Tablename>
group by Empid, gate

• Marked as answer by Tuesday, July 3, 2012 8:57 AM
• Unmarked as answer by Tuesday, July 3, 2012 8:57 AM
Tuesday, July 3, 2012 8:52 AM
• Dear Rohit ,

your query is not correct , your query giving me ist record as minimu entry and todays ax record as exit

i need a time interval on bayout and bayin on daily basis

Thankx & regards, Vipin jha MCP

Tuesday, July 3, 2012 8:58 AM
• try this..

Select Empid, gate, convert(varchar(10), time,112) entrydate, Min(time) EntryTime, Max(time) ExitTime, datediff(mi, Min(time),Max(time)) timediff
from <Tablename>
group by Empid, gate, convert(varchar(10), time,112)

Tuesday, July 3, 2012 9:21 AM
• SQL 2012:

SELECT PassageTime, EmpID, CID, Gate, InOut,
CASE WHEN InOut = 'In' AND
LAG(InOut, 1) OVER (PARTITION BY EmpID, CID ORDER BY PassageTime) = 'Out'
THEN datediff(Minute, LAG(PasageTime, 1)
OVER (PARTITION BY EmpID, CID ORDER BY PassageTime),
PassageTime)
END
FROM   tbl

SQL 2008 & SQL 2005:

WITH numbered AS (
SELECT PassageTime, EmpID, CID, Gate, InOut,
row_number() OVER (PARTITION BY EmpId, CID
ORDER BY PassageTime) AS rowno
FROM   tbl
)
SELECT a.PassageTime, a.EmpID, a.CID, a.Gate, a.Inout,
CASE WHEN a.InOut = 'In' AND b.InOut = 'Out'
THEN datediff(minute, b.PassageTime, a.PassageTime)
END
FROM   numbered a
LEFT   JOIN   numbered b ON b.EmpID = a.EmpID
AND b.CID   = a.CID
AND b.rowno = a.rowno -1

This solution is an outline and is not tested. If you want a tested solution, please provide:

1) CREATE TABLE statement for your table.
2) INSERT statements with sample data.
3) The desired result given the sample.
4) Which version of SQL Server you are using.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, July 3, 2012 9:33 AM
• HI rohit , you query giving me time diffrence between twi datetime

if you see the sasmple data which i post initialy

i am looking for timedifference between logout from bay and next login in bay

Thankx & regards, Vipin jha MCP

Tuesday, July 3, 2012 9:37 AM

• Select T2.Empid, T2.gate, convert(varchar(10), T2.time,112) entrydate, MIN(T2.NextdayEntryTime) NextdayEntryTime, Max(T2.time) ExitTime, datediff(mi, Min(T2.NextdayEntryTime),Max(T2.time)) timediff FORM

(Select Empid, gate, time, (select min(time) from <tablename> t1 where convert(varchar(10), t1.time,112) > convert(varchar(10), T2.time,112)
and t1.empid = T2.empid and t1.gate = T2.gate) NextdayEntryTime from <Tablename>) T2

group by T2.Empid, T2.gate, convert(varchar(10), T2.time,112)

hope it helps !

Tuesday, July 3, 2012 10:02 AM
• Assume that one out must have a corresponding in, this is a pair. Or otherwise it will be a permanent leave.

Also, from your explanation, I understand that you only concern leaving Bays not the reception door.

Not a simple solution, try this:

```Declare @MyTable Table ([Date] Date, Time Time(0), EmpID varchar(10), CID int, Gate varchar(20), InOut varchar(10))

Insert into @MyTable
Select '2012-05-29', '19:32:00', 'M0728', 1, 'Recption Door', 'In' Union All
Select '2012-05-29', '19:33:00', 'M0728', 2, 'Bay 1', 'In' Union All
Select '2012-05-29', '20:45:00', 'M0728', 2, 'Bay 1', 'out' Union All
Select '2012-05-29', '21:01:00', 'M0728', 1, 'Recption Door', 'out' Union All
Select '2012-05-29', '21:11:00', 'M0728', 1, 'Recption Door', 'In' Union All
Select '2012-05-29', '21:12:00', 'M0728', 2, 'Bay 1', 'In' Union All
Select '2012-05-29', '21:13:00', 'M0728', 2, 'Bay 1', 'out' Union All
Select '2012-05-29', '21:13:00', 'M0728', 2, 'Bay 1', 'In' Union All
Select '2012-05-29', '23:01:00', 'M0728', 1, 'Recption Door', 'out' Union All
Select '2012-05-29', '23:12:00', 'M0728', 1, 'Recption Door', 'In'

Select	A.[Date], A.[Time], A.EmpID, A.CID, A.Gate, A.MyTimeOut, B.MyTimeIn
, DATEDIFF(mi, A.MyTimeOut, B.MyTimeIn) As BreakTime
From
(
Select	[Date], [Time], Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120) As MyTimeOut, EmpID, CID, Gate, InOut AS MyOut
From	@MyTable
Where	Gate Like 'Bay%'
And InOut = 'Out'
) A
Outer Apply
(
Select	Top 1 Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120) As MyTimeIn
From	@MyTable C
Where	A.EmpID = C.EmpID
And A.CID = C.CID
And A.Gate = C.Gate
And C.InOut = 'In'
And A.MyTimeOut <= Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)
Order by 	Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)
) B```

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Tuesday, July 3, 2012 10:19 AM
• Hello sir ,

You are like god for me

i changed my query according to logic given by you

Declare @MyTable Table (
[Date] Date,
Time Time(0),
EmpID varchar(10),
Gate varchar(20),
InOut varchar(10))

Insert into @MyTable
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, dt)) [date],
Convert(VARChar, DT, 108) [TIME],
EmpID,
CASE WHEN GtNo='01' THEN 'Recption Door' WHEN GtNo='02' THEN 'Bay 1' else GtNo end gate,
CASE WHEN InOut='0' THEN 'IN' ELSE 'OUT' END INOUT FROM Trans

Select
A.[Date],
A.[Time],
A.EmpID,
A.Gate,
A.MyTimeOut,
B.MyTimeIn,
DATEDIFF(mi, A.MyTimeOut, B.MyTimeIn) As BreakTime
From
(
Select    [Date], [Time], Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120) As MyTimeOut, EmpID,  Gate, InOut AS MyOut
From    @MyTable
Where    Gate Like 'Bay%'
And InOut = 'Out'
) A
Outer Apply
(
Select    Top 1 Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120) As MyTimeIn
From    @MyTable C
Where    A.EmpID = C.EmpID
And A.Gate = C.Gate
And C.InOut = 'In'
And A.MyTimeOut <= Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)
Order by     Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)
) B
where a.EmpID='M0728'
ORDER BY A.Date DESC

but the issue is that its taking much time for a single user also

regards,

Vipin jha

Thankx & regards, Vipin jha MCP

Tuesday, July 3, 2012 11:56 AM
• YOu can create a persisted computed column on your table, like  Mydatetime = Convert( DAtetime, Cast([Date] as varchar(10))+ ' ' + Cast([Time] as varchar(8)), 120)

After that you can create a non clustered index to boost your query perfromance. Like below:

create nonclustered index idx_your_index_Name on Your_Table( Empid, gate, inout, mydatetime) include ([DAte], [Time]);

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

• Marked as answer by Wednesday, July 4, 2012 4:58 AM
Tuesday, July 3, 2012 12:12 PM