Hours Calculation

1

Hi Could you please some body help me to create script for below situation.
The below table captures employee daily login information. The table may have multiple
entries for the same day for one employee.

EmpId Date Timestamp
1 8:00 am 1-Jul-2018
1 9:00 am 1-Jul-2018
1 11:00 am 1-Jul-2018
1 4:00 pm 1-Jul-2018
2 8:15 am 1-Jul-2018
2 5:00 pm 1-Jul-2018

Please provide a script for reporting required in the below format. Sample data given
here.
Sample Output:

EmpId Date Time In Time Out " Total No. Of Hours
Spent in Office"
1 1-Jul-18 8:00 AM 4:00 PM 8
2 1-Jul-18 8:15 am 5:00 PM 8:45

Thanks,
Venugopal.S

2

hi

i tried to do this

hope it helps

:slight_smile: :slight_smile:

drop create data ... 
/***************************************************************************/


-- drop all temp tables 

DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME 
                                     FROM 
                                     tempdb.sys.tables FOR 
                                       xml path( 
          '') ), 1, 1, '')); 

--PRINT @SQL; 
EXEC Sp_executesql 
  @SQL; 


/***************************************************************************/
 
 /*

 create table #data 
 (
 )
 go
  
 INT VARCHAR(100)  DATE DATETIME DECIMAL(10,2) 

 */

 create table #data
 (
 EmpId INT ,
 timestamp time,
 date1 DATE
 )
 go 

 /*

 insert into #data select 

 */

insert into #data select 1,'8:00 am','1-Jul-2018'
insert into #data select 1,'9:00 am','1-Jul-2018'
insert into #data select 1,'11:00 am','1-Jul-2018'
insert into #data select 1,'4:00 pm','1-Jul-2018'
insert into #data select 2,'8:15 am','1-Jul-2018'
insert into #data select 2,'5:00 pm','1-Jul-2018'
go 

/****************************************************************************/

-- select from all temp tables 

DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME + 
                                                    ' go ' 
                 FROM   tempdb.sys.tables 
                 FOR xml path('')), 1, 1, '')); 

--PRINT @SQL1; 
EXEC Sp_executesql 
  @SQL1; 

/***************************************************************************/
SQL .... 
SELECT empid, 
       date1, 
       Min(timestamp) as timein, 
       Max(timestamp) as timeout, 
       Datediff(second, Min(timestamp), Max(timestamp)) / 3600        AS hours, 
       ( Datediff(second, Min(timestamp), Max(timestamp))%3600 ) / 60 AS minutes 
FROM   #data 
GROUP  BY empid, 
          date1 

go

image

3

Thanks a lot its working perfectly fine.

Thanks for your help.:grinning: