how to find overtime in sqlserver

Posted by Klbaiju under Sql Server on 2/17/2014 | Points: 10 | Views : 1031 | Status : [Member] | Replies : 4
Hi,

Following code is working fine

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 hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,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 ),CAST( 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( max(OutTime)-MIN(InTime) AS DATETIME) AS TIME) Extratime
FROM Baiju.dbo.HbaEmp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

empid workingday Empname total_hour(s) Emp_Intime status Extratime

2500 2014-01-01 Arunkumar 10:10 09:40 L 00:30:00.0000000
2502 2014-01-01 Arsh 07:48 06:48 halfday 01:00:00.0000000
2503 2014-01-01 RaJkumar 08:40 08:10 L 00:30:00.0000000
2504 2014-01-01 hari NULL NULL right NULL
2504 2014-01-01 Sini 08:55 08:55 L 00:00:00.0000000

my requirement is to find overtime.

add a field Overtime .and condition is (Emp_Intime-00:45)-Extratime and check if the value is above 8 hour the overtime is value-8 else overtime is 0

for example first record of above should be

empid workingday Empname total_hour(s) Emp_Intime status Extratime overtime

2500 2014-01-01 Arunkumar 10:10 09:40 L 00:30:00.0000000 25

ie (09:40-45)-30.

ie 08:55-30=08:25

overtime is 25 minutes

How it is possible

Regards

Baiju




Responses

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

Up
0
Down
Check this ?

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 hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,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 ),CAST( 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( max(OutTime)-MIN(InTime) AS DATETIME) AS TIME) Extratime ,
CASE WHEN DATEDIFF(MI, '1900-01-01 00:45:00', (max(OutTime)-MIN(InTime)) )- SUM( DATEDIFF( MI, InTime, OutTime)) > 480 THEN DATEDIFF(MI, '1900-01-01 00:45:00', max(OutTime)-MIN(InTime)) - SUM( DATEDIFF( MI, InTime, OutTime)) - 480 ELSE 0 END As OverTime
FROM @emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
GO


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

Up
0
Down
declare @emp table (empid int, workingday date, Empname varchar(100), total_hour time, Emp_Intime time, status varchar(15), Extratime time)
insert @emp
select 2500 , '2014-01-01', 'Arunkumar', '10:10', '09:40', 'L', '00:30:00.0000000' union all
select 2502 , '2014-01-01', 'Arsh', '07:48', '06:48', 'halfday', '01:00:00.0000000' union all
select 2503 , '2014-01-01', 'RaJkumar', '08:40', '08:10', 'L', '00:30:00.0000000' union all
select 2504 , '2014-01-01', 'hari', NULL, NULL, 'right', NULL union all
select 2504, '2014-01-01', 'Sini', '08:55', '08:55', 'L', '00:00:00.0000000'

SELECT
CASE WHEN DATEDIFF(MI, '1900-01-01', (cast(Emp_intime as datetime) - CAST('00:45' as datetime)
- cast( Extratime as datetime))) > 480
THEN DATEDIFF(MI, '1900-01-01', (cast(Emp_intime as datetime) - CAST('00:45' as datetime) - cast( Extratime as datetime)))-480
ELSE 0
END as OVERTIME
FROM @emp


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/18/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Bandi,
I have corrected the code.it is now working fine.
only problem is it returns as int .
following is the code
CASE WHEN DATEDIFF(MI, '1900-01-01', (cast(convert(varchar(5), max(OutTime)-MIN(InTime),108) as datetime) - CAST('00:45' as datetime)
- cast( CAST( 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( max(OutTime)-MIN(InTime) AS DATETIME) AS TIME) as datetime))) > 480
THEN DATEDIFF(MI, '1900-01-01', (cast(convert(varchar(5), max(OutTime)-MIN(InTime),108) as datetime) - CAST('00:45' as datetime) - cast( CAST( 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( max(OutTime)-MIN(InTime) AS DATETIME) AS TIME) as datetime)))-480
ELSE 0
END as OVERTIME
sample out
empid workingday Empname total_hour(s) Emp_Intime status Extratime overtime
2500 2014-01-01 Arunkumar 12:10 11:40 L 00:30:00.0000000 25
overtime should be like 00:25
thanks and Expecting your reply
Regards
Baiju

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

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

Up
0
Down
Click on "Mark As Answer" so that the solution will help others who need similar kind of solution...

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