How to split date column & sum it up

Question

I have a query where i have a date column (time) which tells about "IN" & "OUT" timing of the people attendance by this single column

My queries are :-

1) How to get the daily attendance of each employee 2) How to come to know if the employee is present less than 5 hours

Please let me know the queries in SQL server.

Answer 1

   select 
      datediff(minute, TimeFrom, TimeTo) as AttendedTimeInMinutes,
      case when datediff(minute, sTimeFrom, sTimeTo) < 5 * 60 
         then 
          'less than 5 hours' 
         else '5 hours or more' 
      end
   from YourTable

Answer 2

To find the difference between two datetimes you can use the following:

SELECT Datepart(hour, dateTimeEnd - dateTimeStart)

The DatePart function returns part of a date time variable, and the dateTimeEnd - dateTimeStart returns the difference between two dates as a new DateTime

Answer 3

You'll need to group the query by the user and the items for a particular day then compare the maximum and minimum values, e.g.

declare @users table (
    UserId int, 
    DateColumn datetime
)

insert into @users values (1, '2008-10-31 15:15') 
insert into @users values (1, '2008-10-31 10:30') 
insert into @users values (1, '2008-10-30 16:15') 
insert into @users values (1, '2008-10-30 10:30') 

select
  UserID
  , cast(dt as datetime) dt
  , [in]
  , [out]
  , case when datepart(hour, [out]-[in]) >= 5 then 'yes' else 'no' end [5Hours?], 
  ,  cast(datediff(minute, [in], [out]) as float)/60 [hours] 
from (
  select
    UserID
    , convert(varchar, DateColumn, 112) dt
    , min(DateColumn) [in]
    , max(DateColumn) [out] 
  from @users 
  group by 
    UserID, convert(varchar, DateColumn, 112) 
    ) a