CODEWITHSUNDEEP
sqlsql-server
Time Attendance System
Time Attendance System

Reputation: 319

Calculate time difference from this query?

This is my query:

select  A_In.emp_reader_id as empId,A_In.Belongs_to,B.emp_name,A_In.DeviceSerialNumber,
        DT as EntryTime,
        (
         select min(DT) as OutTime
         from   trnevents A_Out
         where  EVENTID like '0'
         and    A_Out.emp_reader_id = A_In.emp_reader_id
         and    A_Out.DT > A_In.DT and DATEDIFF(day,A_In.Dt,A_Out.DT)=0
        ) as ExitTime


from    trnevents A_In

i need to get calculate difference from entrytime and exittime. I used

DATEDIFF(HOUR,A_In.DT,A_out.ExitTime)

Shows error :The multi-part identifier "A_out.exittime" could not be bound.

Anyone help ..

Upvotes: 0

Views: 62

Answers (3)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

Use cross apply to apply your mindate to your rows, and then take the difference.

Read more about apply here or here

Select A_In.emp_reader_id as empId,A_In.Belongs_to,B.emp_name,A_In.DeviceSerialNumber,
            DT as EntryTime,x.OutTime, Datediff(HOUR,a_in.DT,x.Outtime) as DifferenceTime from trnevents a_in
    CROSS APPLY (select min(DT) as OutTime
             from   trnevents A_Out
             where  EVENTID like '0'
             and    A_Out.emp_reader_id = A_In.emp_reader_id
             and    A_Out.DT > A_In.DT and DATEDIFF(day,A_In.Dt,A_Out.DT)=0
    ) x

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You have basically three choices: a subquery, CTE, or apply. The latter looks like:

select  A_In.emp_reader_id as empId, A_In.Belongs_to, B.emp_name, A_In.DeviceSerialNumber,
        A_In.DT as EntryTime, A_Out.ExitTime,
        datediff(hour, A_In.DT, A_out.ExitTime) as hourdiff
from trnevents A_In outer apply
     (select min(DT) as ExitTime
      from trnevents
      where EVENTID = 0 and
            A_Out.emp_reader_id = A_In.emp_reader_id
            A_Out.DT > A_In.DT and DATEDIFF(day, A_In.Dt, A_Out.DT) = 0
     ) A_Out;

Notes:

  • This is can probably be more efficiently written using window functions.
  • EVENTID is probably a number. In any case, I recommend = rather than LIKE if you don't have wildcards.
  • DATEDIFF() counts the number of hour "transitions" between two values. So, 1:59:59 and 2:00:01 are one hour apart. Often, you want to take the difference in seconds and divide by (60*60).
  • B.emp_name is not defined. I assume this is a mistranscription of your original query.

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

I don't see a Column A_out.exittime names in your Query.

But I assume Using OUTER APPLY may fix your problem.

SELECT A_In.emp_reader_id AS empId,
       A_In.Belongs_to,
       B.emp_name,
       A_In.DeviceSerialNumber,
       DT AS EntryTime,
      ExitTime = E.OutTime
FROM trnevents A_In
    OUTER APPLY
    (
          SELECT MIN(DT) AS OutTime
          FROM trnevents A_Out
          WHERE EVENTID LIKE '0'
               AND A_Out.emp_reader_id = A_In.emp_reader_id
               AND A_Out.DT > A_In.DT
               AND DATEDIFF(day, A_In.Dt, A_Out.DT) = 0
    )E;

Upvotes: 0

Related Questions