CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Calculate total hours for a day

I'm working on a report and I want to display the total time an employee have spent in office for each day. An employee makes in and out of office multiple times so each time the data gets saved. I have table and records like this.

CREATE TABLE Attendance
(
ID Int,
TimeIn datetime,
TimeOut datetime
);


INSERT INTO Attendance VALUES (1, '2018-01-18 09:37:25.000', '2018-01-18 11:12:25.000');
INSERT INTO Attendance VALUES (1, '2018-01-18 11:21:25.000', '2018-01-18 16:32:25.000');
INSERT INTO Attendance VALUES (1, '2018-01-18 16:37:25.000', '2018-01-18 17:55:25.000');
INSERT INTO Attendance VALUES (2, '2018-01-18 09:56:25.000', '2018-01-18 14:37:25.000');
INSERT INTO Attendance VALUES (2, '2018-01-18 15:00:25.000', '2018-01-18 18:27:25.000');
INSERT INTO Attendance VALUES (1, '2018-01-19 09:12:25.000', '2018-01-19 11:41:25.000');
INSERT INTO Attendance VALUES (1, '2018-01-19 13:23:25.000', '2018-01-19 13:31:25.000');
INSERT INTO Attendance VALUES (2, '2018-01-19 09:12:25.000', '2018-01-19 09:59:25.000');
INSERT INTO Attendance VALUES (2, '2018-01-19 12:55:25.000', '2018-01-19 13:12:25.000');
INSERT INTO Attendance VALUES (2, '2018-01-19 14:01:25.000', '2018-01-19 18:10:25.000');
INSERT INTO Attendance VALUES (1, '2018-01-19 17:31:25.000', '2018-01-19 18:51:25.000');

I want the result to be displayed like this.

ID  TimeIn TimeOut TimeIn TimeOut TimeIn TimeOut  TimeSpent          Day
1                                                 Calculated Hours   2018-01-18
2                                                 Calculated Hours   2018-01-18
1                                                 Calculated Hours   2018-01-19
2                                                 Calculated Hours   2018-01-19

And if possible can we make the TimeIn and TimeOut be displayed as well dynamically ?

Upvotes: 0

Views: 243

Answers (2)

Me.Name
Me.Name

Reputation: 12544

The grouping itself can be done with a regular group by, but perhaps are stuck by grouping on the date? If so, the group by clause can have the same cast/conversions used in the display:

SELECT ID, sum(DATEDIFF(MINUTE, TimeIn,TimeOut)) TotalMinutes,CAST(TimeIn as date)  Day
FROM Attendance
group by ID, CAST(TimeIn as date)

Subsequently, formatting of the time can be done with

select ID, format(TotalMinutes / 60, '0') + ':' + format(TotalMinutes % 60, '00'), Day
from(
    SELECT ID, sum(DATEDIFF(MINUTE, TimeIn,TimeOut)) TotalMinutes,CAST(TimeIn as date)  Day
    FROM @Attendance
    group by ID, CAST(TimeIn as date) 
) a

Hadn't seen the added info on the dynamic columns yet. Will try to dive into that later

edit A possibility for the In/Out columns:

;with a as
(
    select *, cast(TimeIn as date) Day ,
        format(dateadd(MINUTE, sum( DATEDIFF( MINUTE , TimeIn,TimeOut)) over (partition by ID,cast(TimeIn as date)),0),'H:mm')  TimeSpent,
        cast(row_number() over (partition by ID, cast(TimeIn as date) order by TimeIn) as varchar) rnr 
    from Attendance
)
select * from 
    (select ID, Day, TimeSpent, 'In' + rnr Name, FORMAT(TimeIn, 'H:mm') T from a
        union all 
        select ID, Day, TimeSpent,'Out' +rnr, FORMAT(TimeOut, 'H:mm') from a) u
pivot(min(T) for Name in ([In1], [Out1], [In2], [Out2],[In3],[Out3], [In4], [Out4])) p --as much as desired

Sql Fiddle

Upvotes: 1

uzi
uzi

Reputation: 4146

Find an employee with maximum number of in/outs in a single day. And change number of columns in the query according to that

select
    id, day, TimeIn = max(iif(rn = 1, TimeIn, '')), TimeOut = max(iif(rn = 1, TimeOut, ''))
    , TimeIn = max(iif(rn = 2, TimeIn, '')), TimeOut = max(iif(rn = 2, TimeOut, ''))
    , TimeIn = max(iif(rn = 3, TimeIn, '')), TimeOut = max(iif(rn = 3, TimeOut, ''))
    , TimeIn = max(iif(rn = 4, TimeIn, '')), TimeOut = max(iif(rn = 4, TimeOut, ''))
    , TimeIn = max(iif(rn = 5, TimeIn, '')), TimeOut = max(iif(rn = 5, TimeOut, ''))
    , TimeSpent = concat(right(concat('0',sum(TimeSpent) / 60),2), ':', right(concat('0',sum(TimeSpent)%60),2))
    , Day
from (
    select
        id, cast(TimeIn as date) day, TimeIn = convert(char(8), TimeIn, 108)
        , TimeOut = convert(char(8), TimeOut, 108)
        , datediff(mi, TimeIn, TimeOut) TimeSpent
        , row_number() over (partition by id, cast(TimeIn as date) order by TimeIn) rn
    from 
        @Attendance
) t
group by id, day

Upvotes: 1

Related Questions