locked
Logout and Login Time difference very difficult to find RRS feed

  • 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.

    Please suggest me,

    for last 2 days i am working on same.

    Regards,

    Vipin jha



    Thankx & regards, Vipin jha MCP

    Tuesday, July 3, 2012 8:27 AM

Answers

  • 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


    View Steven Wang's profile on LinkedIn | 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]);


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

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

    • Marked as answer by Vipin jha 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

    Please click the Mark as Answer or Vote As Helpful  if a post solves your problem or is helpful!

    • Marked as answer by Vipin jha Tuesday, July 3, 2012 8:57 AM
    • Unmarked as answer by Vipin jha 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)

    Please click the Mark as Answer or Vote As Helpful  if a post solves your problem or is helpful!

    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


    View Steven Wang's profile on LinkedIn | 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]);


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

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

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