find time difference in sqlserver

Posted by Klbaiju under Sql Server on 2/27/2014 | Points: 10 | Views : 4078 | Status : [Member] | Replies : 6
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




Responses

Posted by: Bandi on: 2/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down

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')

--Can you explain how this output U expected?
Empid workingday Empname total work_hour(s) difference
2500 2014-01-01 Arun 11:00 09:30

How this '2014-01-01 01:30:00:00' is minimum and '2014-01-01 12:30:0' is maximum ?
If you are having input & output time data as AM/PM, try to have same standard for each row..

One question?
If you considered '2014-01-01 01:30:00:00' as PM then have 13:30:00 ,
else have '2014-01-01 18:30:0' as '2014-01-01 06:30:0 PM '

As per my understanding your input data is incorrect in the second scenario...

i.e.

Have either of below two:

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 13:30:00:00','2014-01-01 18:30:0')


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 PM ','2014-01-01 06:30:0 PM')




Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 2/28/2014 [Member] Starter | Points: 25

Up
0
Down
Hi bandi,
I have changed the the time as 24 hour style it is working.
you have corrected same code when i make the dummy application.
next i want to deduct 45 minutes from total work_hours as totaltime and i need to calculate another field overtime(check totaltime greater than 8 hour excess time is overtime.
i tried this code
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 11:30:0')
insert into #emp1 values(2500,'Arun','2014-01-01 12:30:00:00','2014-01-01 19:30:0')
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,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, inTime, outTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, inTime, outTime))%60), 2) - CAST('00:45' as datetime) totaltime
FROM #emp1
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
got the output as
Empid workingday Empname total work_hour(s) difference totaltime
2500 2014-01-01 Arun 09:00 10:30 1900-01-01 08:15:00.000
i need totaltime should be 08:15 and calculate overtime(check totaltime greater than 8 hour excess time is overtime)

Regards
Baiju

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down

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 11:30:0')
insert into #emp1 values(2500,'Arun','2014-01-01 12:30:00:00','2014-01-01 19:30:0')

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
,CAST( DATEADD( MINUTE, SUM( DATEDIFF( MI, inTime, outTime))-45, 0) AS TIME)TotalTime
,CAST( DATEADD( MINUTE, CASE WHEN SUM( DATEDIFF( MI, inTime, outTime)) > 525 THEN SUM( DATEDIFF( MI, inTime, outTime))-525 ELSE 0 END, 0 ) AS TIME ) OverTime
FROM #emp1
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
drop table #emp1

/*
EmpId workingday EmpName total work_hour(s) Difference TotalTime OverTime
2500 2014-01-01 Arun 09:00 10:30 08:15:00.0000000 00:15:00.0000000
*/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark This Response as Answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 3/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi bandi,
following code returns
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 11:00:0')
insert into #emp1 values(2500,'Arun','2014-01-01 11:30:00:00','2014-01-01 19:00:00')

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
,CAST( DATEADD( MINUTE, SUM( DATEDIFF( MI, inTime, outTime))-45, 0) AS TIME)TotalTime,
CAST( DATEADD( MINUTE, CASE WHEN SUM( DATEDIFF( MI, inTime, outTime)) > 525 THEN SUM( DATEDIFF( MI, inTime, outTime))-525 ELSE 0 END, 0 ) AS TIME ) OverTime
FROM #emp1
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
drop table #emp1
as
EmpId workingday EmpName total work_hour(s) Difference TotalTime OverTime
2500 2014-01-01 Arun 09:00 10:00 08:15:00.0000000 00:15:00.0000000
here totaltime is 08:15 and overtime is 00:15 every thing is fine
i need another field actualtime
ie if totaltime is greater than 8hour then actual time is difference of totaltime and overtime here actual time should be 08:00
if totaltime is lessthan 08:00 actualtime is same also i want to avoid excess zeros from total time and overtime

how to solve this

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/5/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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 11:00:0')
insert into #emp1 values(2500,'Arun','2014-01-01 11:30:00:00','2014-01-01 19:00:00')

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
,CONVERT(VARCHAR(5), DATEADD( MINUTE, SUM( DATEDIFF( MI, inTime, outTime))-45, 0) , 108 )TotalTime
,CONVERT(VARCHAR(5),DATEADD( MINUTE, CASE WHEN SUM( DATEDIFF( MI, inTime, outTime)) > 525 THEN SUM( DATEDIFF( MI, inTime, outTime))-525 ELSE 0 END, 0 ), 108 ) OverTime
,CONVERT(VARCHAR(5), DATEADD( MINUTE, CASE WHEN SUM( DATEDIFF( MI, inTime, outTime)) > 525 THEN 480 ELSE SUM( DATEDIFF( MI, inTime, outTime)) END, 0 ), 108 ) ActualTime
FROM #emp1
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

drop table #emp1


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response