find time difference in sqlserver

Posted by Klbaiju under Sql Server on 2/27/2014 | Points: 10 | Views : 4430 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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


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

0	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

0	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

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

0	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

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

Latest Posts