Get Min In time and Max out TIme. Fom Attendance record in Sql Server

Question

I am working on attendance system, and below is my data in sql server database

|EmpCode|Date       |WorkDate   |CheckIn    |CheckOut   |TotalTime
|143    |2017-02-13 |2017-02-13 |20:09:02   |22:38:50   |2.496666000
|143    |2017-02-13 |2017-02-13 |22:59:19   |23:18:15   |0.315555000
|143    |2017-02-13 |2017-02-13 |23:33:47   |05:04:24   |5.510277000
|143    |2017-02-14 |2017-02-13 |02:52:38   |05:12:04   |2.323888000
|143    |2017-02-14 |2017-02-14 |20:09:26   |21:59:27   |1.833611000
|143    |2017-02-14 |2017-02-14 |22:30:10   |22:49:26   |0.321111000
|143    |2017-02-15 |2017-02-14 |05:05:05   |05:08:13   |0.052222000

-- I want the output like

|EmpCode|wrk        |InTime     |OutTime    |TotalTime
|143    |2017-02-13 |08:09:02 PM|05:12:04 AM|10:38:00
|143    |2017-02-14 |08:09:26 PM|05:08:13 AM|02:12:00

SQLzim had a good answer. they just needed to concat the date for checkin and checkout times — xQbert, Feb 15 '17 at 13:03
How did you get those totals? Are you using symbolic values? — gjfonte, Feb 15 '17 at 13:10
sum total time. you get 10.646 or there abouts. 10 is hours. take .646 * 60 and you get 38 roughly. — xQbert, Feb 15 '17 at 13:12
I took the reference of total time from http://stackoverflow.com/questions/20882934/calculate-time-difference-for-attendance — Goutam Singh, Feb 15 '17 at 13:23
which version are you using... you tagged 4... remove the tags that don't fit your version. — S3S, Feb 15 '17 at 15:07

SqlZim · Accepted Answer · 2017-02-15T13:14:03.547

Assuming you want those specific formats for times:

rextester: http://rextester.com/FUW31296

create table t (
     EmpCode int
  ,  Date datetime
  ,  WorkDate datetime
  ,  CheckIn datetime
  ,  CheckOut datetime
  ,  TotalTime decimal(19, 9))

insert into t values
 (143,'2017-02-13','2017-02-13','20:09:02','22:38:50',2.496666000)
,(143,'2017-02-13','2017-02-13','22:59:19','23:18:15',0.315555000)
,(143,'2017-02-13','2017-02-13','23:33:47','05:04:24',5.510277000)
,(143,'2017-02-14','2017-02-13','02:52:38','05:12:04',2.323888000)
,(143,'2017-02-14','2017-02-14','20:09:26','21:59:27',1.833611000)
,(143,'2017-02-14','2017-02-14','22:30:10','22:49:26',0.321111000)
,(143,'2017-02-15','2017-02-14','05:05:05','05:08:13',0.052222000);

query:

select 
     EmpCode
  ,  wrk       = convert(varchar(10),WorkDate,120)
  ,  InTime    = format(cast(min(Date + CheckIn) as datetime), 'hh:mm:ss tt')
  ,  OutTime   = format(cast(max(Date + CheckOut) as datetime), 'hh:mm:ss tt')
  ,  TotalTime = format(dateadd(minute,sum(TotalTime)*60,0), 'HH:mm:ss')
from t
group by 
    EmpCode
  , WorkDate

results:

+---------+------------+-------------+-------------+-----------+
| EmpCode |    wrk     |   InTime    |   OutTime   | TotalTime |
+---------+------------+-------------+-------------+-----------+
|     143 | 2017-02-13 | 08:09:02 PM | 05:12:04 AM | 10:38:00  |
|     143 | 2017-02-14 | 08:09:26 PM | 05:08:13 AM | 02:12:00  |
+---------+------------+-------------+-------------+-----------+

You were definitely on the right path before the deleted answer and the update looks good! — xQbert, Feb 15 '17 at 13:26
Thanks. I backed out when I noticed the formats weren't going to be what was specified in the result and wanted to test before restoring. — SqlZim, Feb 15 '17 at 13:31
Oh well. I'll remove the link. Thanks. but i'll keep the gist of the exchange. I still find it funny. — xQbert, Feb 15 '17 at 13:52

score 1 · Answer 2 · answered Dec 10 '17 at 12:01

select  *
,       datediff(minute, first_in, last_out) as Totaltime
from    (
        select  emp_reader_id
        ,       min(case when Event_entry.event_entry_name = 'IN' then trnevents.DT end) as Intime
        ,       max(case when Event_entry.event_entry_name = 'OUT' then trnevents.DT end) as Outtime
        ,       cast(min(trnevents.DT) as date) as date
        from    trnevents inner join Event_entry on trnevents.EventCatId=Event_entry.EventCatId
        group by
                emp_reader_id
        ,       cast(trnevents.DT as date)
        ) as SubQueriesMustBeNamed

score 0 · Answer 3 · answered Feb 15 '17 at 13:11

0

select EmpCode, WorkDate as wrk, min(InTime) as InTime, max(OutTime) as OutTime, Sum(TotalTime) as TotalTime
from yourtable
group by EmpCode, WorkDate

will give you the smallest InTime, the bigest OutTime and the total TotalTime from yourtable per user per day.

answered Feb 15 '17 at 13:11

Lajos Arpad

64,414
37
100
175

Don't think this handles the 4th line correctly as the "Min" checkin would be 02:52:38 and total time is not in the right format. – xQbert Feb 15 '17 at 13:27

Get Min In time and Max out TIme. Fom Attendance record in Sql Server

3 Answers3