how to deduct a date from a number

Posted by Klbaiju under Sql Server on 2/13/2014 | Points: 10 | Views : 1161 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

0	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

0	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

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

Latest Posts