how to deduct a date from a number

Posted by Klbaiju under Sql Server on 2/13/2014 | Points: 10 | Views : 691 | Status : [Member] | Replies : 5
Hi,
I want to deduct a date from a number.
following is the code and working fine
SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay,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(time, max(OutTime)-MIN(InTime)) as Emp_Intime
FROM [Baiju].[dbo].[HbaEmp]
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

i want to display overtime.
condition is i want deduct Emo_Intime from total work_hour(s)
following is sample data
total work_hour(s) Emp_Intime
10:20 09:40:00.0000000
condition is i want deduct Emo_Intime from total work_hour(s)
ie 10:20 - 09:40:00.0000000
how it is possible
Regards
Baiju




Responses

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

Up
0
Down
SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay
,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(time, max(OutTime)-MIN(InTime)) as Emp_Intime
,CAST( max(OutTime)-MIN(InTime) - 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) AS DATETIME) AS TIME)
FROM [Baiju].[dbo].[HbaEmp]
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)


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: Kshaik on: 2/14/2014 [Member] Starter | Points: 25

Up
0
Down
SELECT EmpId ,
EmpName,
CAST(InTime AS DATE) WorkingDay,

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)], ---10:05

CONVERT(TIME, MAX(OutTime)-MIN(InTime)) AS Emp_Intime ,

CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,
CONVERT(DATETIME, max(OutTime)-MIN(InTime)) ,
CONVERT(DATETIME,DATEADD(MI,CAST(RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS INT),DATEADD(HOUR ,CAST(RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) AS INT),CONVERT(TIME, max(OutTime)-MIN(InTime)))))),0), 108)AS Time

FROM HbaEmp

GROUP BY EmpId , EmpName, CAST(InTime AS DATE) ;

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

Posted by: Kshaik on: 2/14/2014 [Member] Starter | Points: 25

Up
0
Down
Please check it once

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

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

Up
0
Down
both codes not working

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

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

Up
0
Down
I think my query will satisfy your requirement... As per my understanding you want to get the total out time in a day per employee.. If this is the one, you go ahead with below query:

DECLARE @Emp TABLE(EmpId INT, EmpName VARCHAR(10), InTime DATETIME, OutTime DATETIME, dstatus VARCHaR(5), whours INT)
insert @Emp
SELECT 2500, 'Arun', '2014-01-01 08:30', '2014-01-01 09:30', NULL , NULL union all
SELECT 2500, 'Arun', '2014-01-01 09:40', '2014-01-01 11:30', NULL , NULL union all
SELECT 2500, 'Arun', '2014-01-01 12:30', '2014-01-01 12:45', NULL , NULL union all
SELECT 2501, 'Kiran', '2014-01-01 14:10:00.000', '2014-01-01 18:30', NULL, NULL union all
SELECT 2502, 'Arsh', '2014-01-01 11:12:00.000', '2014-01-01 16:30', NULL, NULL
--i want to find out the total working hours of each employee

SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay
,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(time, max(OutTime)-MIN(InTime)) as Emp_Intime
,CAST( max(OutTime)-MIN(InTime) - 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) AS DATETIME) AS TIME) OutTimeHH:MI:SS
FROM @Emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)


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