Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 965
Home > Forums > Sql Server >

Error in calculating time difference in sqlserver [Resolved]

Posted by Klbaiju under Sql Server on 8/4/2014 | Points: 10 | Views : 2744 | Status : [Member] | Replies : 8
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
Hi,

iam working in an employee management application in which i want to find out intime, outtime, overtime of each employee in a day.

following is working code
create table #temp(empid numeric(18,0),empname nvarchar(50),intime datetime,outtime datetime)
insert into #temp values(2500,'Sachin','2014-01-01 09:00:00','2014-01-01 10:30:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 11:00:00','2014-01-01 12:30:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 13:30:00','2014-01-01 20:30:00:00')
select EmpId ,CONVERT(VARCHAR(20), InTime,106 ) as workingday,left(CONVERT(TIME,MIN(InTime),108),5)as intime,left(CONVERT(TIME,max(outTime),108),5)as outtime ,dstatus=
(CASE
 WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00'
 THEN 'L'
  WHEN CONVERT(TIME,MIN(InTime),108)>'11:00'
  THEN 'halfday'
  else 'right' end ),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) AS [total work_hour(s)]
        
        ,left(cast(CAST(DATEADD(MINUTE, SUM(DATEDIFF(MI, inTime, outTime))-45, 0) AS TIME) as varchar),5) TotalTime,
        left(cast(CAST(DATEADD(MINUTE, CASE WHEN SUM(DATEDIFF(MI, inTime, outTime)) > 525 THEN SUM(DATEDIFF(MI, inTime, outTime))-525 ELSE 0 END, 0 ) AS TIME ) as varchar),5) OverTime,
       right('0'+CAST((datediff(mi,CAST(DATEADD(MINUTE, CASE WHEN SUM(DATEDIFF(MI, inTime, outTime)) > 525 THEN SUM(DATEDIFF(MI, inTime, outTime))-525 ELSE 0 END, 5 ) AS TIME ),CAST(DATEADD(MINUTE, SUM(DATEDIFF(MI, inTime, outTime))-45, 0) AS TIME)) / 60) AS VARCHAR(8)),3)
       +':'+right('0'+CAST((datediff(mi,CAST(DATEADD(MINUTE, CASE WHEN SUM(DATEDIFF(MI, inTime, outTime)) > 525 THEN SUM(DATEDIFF(MI, inTime, outTime))-525 ELSE 0 END, 0 ) AS TIME ),CAST(DATEADD(MINUTE, SUM(DATEDIFF(MI, inTime, outTime))-45, 0) AS TIME)) % 60) AS VARCHAR(2)), 2) ActualTime
FROM #temp  GROUP BY EmpId , CONVERT(VARCHAR(20), InTime,106 )
drop table #temp

its output is
EmpId    workingday    intime    outtime    dstatus    Difference    total work_hour(s)    TotalTime    OverTime    ActualTime
2500    01 Jan 2014    09:00    20:30            L            11:30           10:00                        09:15           01:15       08:00

this is working fine.

following is calcultion of each field

difference means difference of maximum outtime-minimum intime

total work_hour(s) means Diffrence- sparetime

here an employee with id 2500 arrives at 09:00 and gone out side at 10:30 again come at 11:00 here he spends 30 minutes outside.there is total 01:30 spare time in this table

Totaltime means total work_hour(s)-45(lunch time)

Actual time means it is fixed 8 hour a day

overtime means the time exceeds 08 hrs.

if total time is more than 08 hrs overtime =Totaltime-actualtime else overtime is 0.

here my requirement is lunch time is from 12:45 to 13:30 .if anyone go outside on this time this shouldn't be cakculated.in this table the employee gone outside at 10:30 and come back at 11:00 here spare time is 00:30 hrs.

he again gone outside at 12:30and come back at 13:30 .here is the error according to query ,the spare time is 01:00 hrs.

but my requirement is it should be 15 minutes means from 12:30 to 12:45 . because 12:45 to 13:30 is lunch time.

suppose any gone outside at 12:30 and come back at 13:45 sparetime should be 15+15=00:30 hrs.

finally my output should be like this

EmpId    workingday    intime    outtime    dstatus    Difference    total work_hour(s)    TotalTime    OverTime    ActualTime
2500      01 Jan 2014    09:00    20:30          L      
                           
                


                    
                    


                
                           

                
Reply | Reply with Attachment

                               

 Alert Moderator 

Responses

Posted by: Bandi on: 8/5/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

create table #temp(empid numeric(18,0),empname nvarchar(50),intime datetime,outtime datetime)



insert into #temp values(2500,'Sachin','2014-01-01 09:00:00','2014-01-01 10:30:00:00')



insert into #temp values(2500,'Sachin','2014-01-01 11:00:00','2014-01-01 12:30:00:00')



insert into #temp values(2500,'Sachin','2014-01-01 13:45:00','2014-01-01 20:30:00:00')





;with cte as (SELECT ROW_NUMBER() OVER(PARTITION BY empid, CONVERT(VARCHAR(20), InTime,106 ) ORDER by intime) rn, * FROM #temp)

SELECT c1.EmpId 

	,CONVERT(VARCHAR(20), c1.InTime,106 ) as workingday

	,left(CONVERT(TIME,MIN(c1.InTime),108),5)as intime

	,left(CONVERT(TIME,max(c1.outTime),108),5)as outtime 

	,dstatus=(CASE WHEN CONVERT(TIME,MIN(c1.InTime),108)>'08:35' and CONVERT(TIME,MIN(c1.InTime),108) <'11:00' THEN 'L' WHEN CONVERT(TIME,MIN(c1.InTime),108)>'11:00' THEN 'halfday' ELSE 'right' end )

	,convert(varchar(5), max(c1.OutTime)-MIN(c1.InTime),108) as [Difference]

	,RIGHT('0'+CONVERT(varchar, FLOOR(SUM(CASE WHEN cast(c1.outtime as time) <= '12:45:00' and CAST(c2.intime AS time) >= cast('13:30:00' AS time) THEN DATEDIFF(MI, cast(c1.outtime as time), '12:45:00') +DATEDIFF(MI,  '13:30:00', cast(c2.intime as time)) ELSE DATEDIFF(MI, c1.outtime , c2.intime) end)/60.0)),2)

		+':'+RIGHT('0'+CONVERT(varchar, FLOOR(SUM(CASE WHEN cast(c1.outtime as time) <= '12:45:00' and CAST(c2.intime AS time) >= cast('13:30:00' AS time) THEN DATEDIFF(MI, cast(c1.outtime as time), '12:45:00') +DATEDIFF(MI,  '13:30:00', cast(c2.intime as time)) else DATEDIFF(MI, c1.outtime , c2.intime) end)%60.0)),2) OutofOfcWithoutLunchTime

	,convert(varchar(5), max(c1.OutTime)-MIN(c1.InTime)-DATEADD( Minute,SUM(CASE WHEN cast(c1.outtime as time) <= '12:45:00' and CAST(c2.intime AS time) >= cast('13:30:00' AS time) THEN DATEDIFF(MI, cast(c1.outtime as time), '12:45:00') +DATEDIFF(MI,  '13:30:00', cast(c2.intime as time)) else DATEDIFF(MI, c1.outtime , c2.intime) end) , 0), 108) TotalWorkingHours

	,RIGHT('0'+CONVERT(varchar, FLOOR(DATEDIFF(MI, DATEADD(Mi, 45, '1900-01-01'),max(c1.OutTime)-MIN(c1.InTime)-DATEADD( Minute,SUM(CASE WHEN cast(c1.outtime as time) <= '12:45:00' and CAST(c2.intime AS time) >= cast('13:30:00' AS time) THEN DATEDIFF(MI, cast(c1.outtime as time), '12:45:00') +DATEDIFF(MI,  '13:30:00', cast(c2.intime as time)) else DATEDIFF(MI, c1.outtime , c2.intime) end) , 0))/60.0)),2)+':'+RIGHT('0'+CONVERT(varchar, FLOOR(DATEDIFF(MI, DATEADD(Mi, 45, '1900-01-01'),max(c1.OutTime)-MIN(c1.InTime)-DATEADD( Minute,SUM(CASE WHEN cast(c1.outtime as time) <= '12:45:00' and CAST(c2.intime AS time) >= cast('13:30:00' AS time) THEN DATEDIFF(MI, cast(c1.outtime as time), '12:45:00') +DATEDIFF(MI,  '13:30:00', cast(c2.intime as time)) else DATEDIFF(MI, c1.outtime , c2.intime) end) , 0))%60.0)),2) TotalTime

	,left(cast(CAST(DATEADD(MINUTE, CASE WHEN DATEDIFF(MI, DATEADD(Mi, 45, '1900-01-01'),max(c1.OutTime)-MIN(c1.InTime)-DATEADD( Minute,SUM(CASE WHEN cast(c1.outtime as time) <= '12:45:00' and CAST(c2.intime AS time) >= cast('13:30:00' AS time) THEN DATEDIFF(MI, cast(c1.outtime as time), '12:45:00') +DATEDIFF(MI,  '13:30:00', cast(c2.intime as time)) else DATEDIFF(MI, c1.outtime , c2.intime) end) , 0))>480  

	THEN DATEDIFF(MI, DATEADD(Mi, 45, '1900-01-01'),max(c1.OutTime)-MIN(c1.InTime)-DATEADD( Minute,SUM(CASE WHEN cast(c1.outtime as time) <= '12:45:00' and CAST(c2.intime AS time) >= cast('13:30:00' AS time) THEN DATEDIFF(MI, cast(c1.outtime as time), '12:45:00') +DATEDIFF(MI,  '13:30:00', cast(c2.intime as time)) else DATEDIFF(MI, c1.outtime , c2.intime) end) , 0))-480 ELSE 0 END, 0) AS TIME) as varchar),5) TotalTime

	,right('0'+CAST((datediff(mi,CAST(DATEADD(MINUTE, CASE WHEN SUM(DATEDIFF(MI, c1.inTime, c1.outTime)) > 525 THEN SUM(DATEDIFF(MI, c1.inTime, c1.outTime))-525 ELSE 0 END, 5 ) AS TIME ),CAST(DATEADD(MINUTE, SUM(DATEDIFF(MI, c1.inTime, c1.outTime))-45, 0) AS TIME)) / 60) AS VARCHAR(8)),3) +':'+right('0'+CAST((datediff(mi,CAST(DATEADD(MINUTE, CASE WHEN SUM(DATEDIFF(MI, c1.inTime, c1.outTime)) > 525 THEN SUM(DATEDIFF(MI, c1.inTime, c1.outTime))-525 ELSE 0 END, 0 ) AS TIME ),CAST(DATEADD(MINUTE, SUM(DATEDIFF(MI, c1.inTime, c1.outTime))-45, 0) AS TIME)) % 60) AS VARCHAR(2)), 2) ActualTime

FROM cte c1

LEFT join cte c2 on c1.rn = c2.rn-1 and c1.empid = c2.empid and CONVERT(VARCHAR(20), c1.InTime,106 ) = CONVERT(VARCHAR(20), c2.InTime,106 ) 

GROUP BY c1.EmpId , CONVERT(VARCHAR(20), c1.InTime,106 )

drop table #temp


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: 8/13/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
create table #temp(empid numeric(18,0),empname nvarchar(50),intime datetime,outtime datetime)


insert into #temp values(2500,'Sachin','2014-01-01 09:00:00','2014-01-01 10:30:00:00')
--insert into #temp values(2500,'Sachin','2014-01-01 11:00:00','2014-01-01 12:45:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 11:00:00','2014-01-01 12:55:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 13:40:00','2014-01-01 20:30:00:00')


;with cte as (SELECT ROW_NUMBER() OVER(PARTITION BY empid, CONVERT(VARCHAR(20), InTime,106 ) ORDER by intime) rn, * FROM #temp)
SELECT c1.EmpId, CONVERT(VARCHAR(20), c1.InTime,106 ) as workingday
        ,left(CONVERT(TIME,MIN(c1.InTime),108),5)as intime
        ,left(CONVERT(TIME,max(c1.outTime),108),5)as outtime 
        ,dstatus=(CASE WHEN CONVERT(TIME,MIN(c1.InTime),108)>'08:35' and CONVERT(TIME,MIN(c1.InTime),108) <'11:00' THEN 'L' WHEN CONVERT(TIME,MIN(c1.InTime),108)>'11:00' THEN 'halfday' ELSE 'right' end )
        ,convert(varchar(5), max(c1.OutTime)-MIN(c1.InTime),108) as [Difference]
        ,CAST(DATEADD( Mi, SUM(CASE 
        WHEN Cast(c1.outtime AS time) <= '12:45:00' and cast(c2.intime as time) >= '13:30:00' 
        THEN DATEDIFF( MI,CAST(c1.outtime as time), '12:45') 
		  +  DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) <= '13:30' THEN 0     
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) >= '13:30' 
		THEN DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		ELSE DATEDIFF(MI, c1.outtime, c2.intime) END), 0) AS TIME(0)) OutOfOfcWithoutLunch
		
		,CAST(max(c1.OutTime)-MIN(c1.InTime) -DATEADD( Mi, SUM(CASE 
        WHEN Cast(c1.outtime AS time) <= '12:45:00' and cast(c2.intime as time) >= '13:30:00' 
        THEN DATEDIFF( MI,CAST(c1.outtime as time), '12:45') 
		  +  DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) <= '13:30' THEN 0     
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) >= '13:30' 
		THEN DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		ELSE DATEDIFF(MI, c1.outtime, c2.intime) END), 0) AS TIME(0)) TotalWorkingHours
		
		,CAST(max(c1.OutTime)-MIN(c1.InTime) -DATEADD( Mi, SUM(CASE 
        WHEN Cast(c1.outtime AS time) <= '12:45:00' and cast(c2.intime as time) >= '13:30:00' 
        THEN DATEDIFF( MI,CAST(c1.outtime as time), '12:45') 
		  +  DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) <= '13:30' THEN 0     
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) >= '13:30' 
		THEN DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		ELSE DATEDIFF(MI, c1.outtime, c2.intime) END), 0) - DATEADD( MI,  45,'1900-01-01') AS TIME(0)) TotalTime
	
		,CASE WHEN DATEPART( HH, max(c1.OutTime)-MIN(c1.InTime) -DATEADD( Mi, SUM(CASE 
        WHEN Cast(c1.outtime AS time) <= '12:45:00' and cast(c2.intime as time) >= '13:30:00' 
        THEN DATEDIFF( MI,CAST(c1.outtime as time), '12:45') 
		  +  DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) <= '13:30' THEN 0     
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) >= '13:30' 
		THEN DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		ELSE DATEDIFF(MI, c1.outtime, c2.intime) END), 0) - DATEADD( MI,  45,'1900-01-01')) > 8
		THEN	CAST( max(c1.OutTime)-MIN(c1.InTime) -DATEADD( Mi, SUM(CASE 
        WHEN Cast(c1.outtime AS time) <= '12:45:00' and cast(c2.intime as time) >= '13:30:00' 
        THEN DATEDIFF( MI,CAST(c1.outtime as time), '12:45') 
		  +  DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) <= '13:30' THEN 0     
		WHEN CAST(c1.outtime as time) >= '12:45' and CAST( c2.intime as time) >= '13:30' 
		THEN DATEDIFF( MI, '13:30', CAST(c2.intime as time))  
		ELSE DATEDIFF(MI, c1.outtime, c2.intime) END), 0) - DATEADD( MI,  45,'1900-01-01') - DATEADD( MI,  480,'1900-01-01')	AS TIME(0)) ELSE '00:00:00' END ExceedTime
		,CAST(DATEADD( MI,  480,'1900-01-01')	AS TIME(0)) ActualTime
	FROM cte c1
LEFT join cte c2 on c1.rn = c2.rn-1 and c1.empid = c2.empid and CONVERT(VARCHAR(20), c1.InTime,106 ) = CONVERT(VARCHAR(20), c2.InTime,106 ) 
GROUP BY c1.EmpId , CONVERT(VARCHAR(20), c1.InTime,106 )
GO
drop table #temp


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: 8/6/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Have you fixed the issue with above solution? If NOT, post us back the where you have doubts......

if resolved the issue, mark above post 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: 8/6/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Bindi,
Thanks for your reply.i know you tried your best for solving my problem.
yuor code is working if we use following data
insert into #temp values(2500,'Sachin','2014-01-01 09:00:00','2014-01-01 10:30:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 11:00:00','2014-01-01 12:45:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 13:30:00','2014-01-01 20:30:00:00')

output is 
EmpId	workingday	intime	outtime	dstatus	Difference	OutofOfcWithoutLunchTime	TotalWorkingHours	TotalTime	OverTime	ActualTime
2500	01 Jan 2014	09:00	20:30	L	11:30	00:30	11:00	10:15	02:15	08:00

this is fine
if we change second sample data to
insert into #temp values(2500,'Sachin','2014-01-01 11:00:00','2014-01-01 12:50:00:00')
then output is
EmpId	workingday	intime	outtime	dstatus	Difference	OutofOfcWithoutLunchTime	TotalWorkingHours	TotalTime	OverTime	ActualTime
2500	01 Jan 2014	09:00	20:30	L	11:30	01:10	10:20	09:35	01:35	08:00

this is the error
the output should be same.because
if any go outside between 12:45 and 13:30 this time should not be calculated.
you added a new field OutofOfcWithoutLunchTime that is fine
Regards
Baiju


Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/13/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Post me back if you have nay other scenarios...

Test the above T-SQL script for you requiremnts and let me know the results

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: 8/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark as Answer if got clear idea/solution...

Post us back the issue/doubts if not resolved the issue

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: 9/2/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Baiju,

Did you resolve this issue? If Yes, Mark it as Answer; otherwise post me back the problem where it is not functioning well..

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: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

Please elaborate your requirement as per Microsoft terminology then only people get understand the post/requirement.

As per my understanding you need to calculate the difference between 2 times right.?

If you are looking for the same then use below sample syntax.


select DataDiff(mi,@from_date,@to_date);


Try something like above to achieve your goal.

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

Login to post response

Latest Posts