hi,
I want to calculate time difference from a table
if i execute following code
create table #emp(empid varchar(50),empname varchar(50),intime smalldatetime,outtime smalldatetime)
insert into #emp values(2500,'Arun','2014-01-01 09:00:00','2014-01-01 18:30:0')
insert into #emp values(2500,'Arun','2014-01-01 10:30:00:00','2014-01-01 11:30:0')
insert into #emp values(2500,'Arun','2014-01-01 14:30:00:00','2014-01-01 15:00:0')
--drop table #emp
--select * from #emp
select EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Difference
FROM #emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
drop table #emp
Empid workingday Empname total work_hour(s) difference
2500 2014-01-01 Arun 11:00 09:30
this is working fine because min(intime) and max(outtime) is in same record
My requirement is ,in my application min(intime) and max(outtime) is in
different record
if i execute this
create table #emp1(empid varchar(50),empname varchar(50),intime smalldatetime,outtime smalldatetime)
insert into #emp1 values(2500,'Arun','2014-01-01 09:00:00','2014-01-01 10:30:0')
insert into #emp1 values(2500,'Arun','2014-01-01 11:00:00:00','2014-01-01 12:30:0')
insert into #emp1 values(2500,'Arun','2014-01-01 01:30:00:00','2014-01-01 18:30:0')
select EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, outTime, inTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, outTime, inTime))%60), 2) AS [total work_hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Difference
FROM #emp1
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
drop table #emp1
i got the output as
Empid Workingday Empname total work_hour(s) Difference
2500 2014-01-01 Arun 20:00 17:00
i need the out like
Empid workingday Empname total work_hour(s) difference
2500 2014-01-01 Arun 11:00 09:30
I want to correct the second code.
any one can solve thanks in advance
Baiju