calaculate difference between time

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
In my application ,I want to find out intime and outtime of employees

a table named empdailystatus containing empid,empname,intime,outtime,dstatus,whours
if intime > 08:35am and intime < 11:00am dstatus=L

if intime >11:00 am dstatus= halfday.

and find out whours
ie how much time an employee works in office using intime and outtime

how it is possible

Regards
K L BAIJU

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sravan661 on: 2/11/2014 [Member] Bronze | Points: 25

0	Hi, create procedure SpDailyWhours ( @empid int, @empname varchar(max), @intime datetime, @outtime datetime, @dstatus varchar(10) ) as declare @whours time = @outtime - @intime select @whours //Your insert query here Hope this helps you mark as ANSWER if satisfied sravan Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,


create procedure SpDailyWhours
(
@empid int,
@empname  varchar(max),
@intime datetime,
@outtime datetime,
@dstatus varchar(10)
)
as
declare @whours time  = @outtime - @intime
select @whours 
//Your insert query here

Hope this helps you
mark as ANSWER if satisfied

sravan

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

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

0	It depends on input data. An employee can do more than one in and out ( i.e. for breaks).. Could you please post us back some sample data? We can provide exact 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

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

0	http://stackoverflow.com/questions/7244252/how-to-design-database-table-for-working-hours 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/12/2014 [Member] Starter | Points: 25

0	Hi Mr Bindi these are sample datas EmpId EmpName InTime OutTime dstatus whours 2500 Arun 2014-01-01 08:30 2014-01-01 09:30 NULL NULL 2500 Arun 2014-01-01 09:40 2014-01-01 11:30 2500 Arun 2014-01-01 12:30 2014-01-01 12:45 2501 Kiran 2014-01-01 14:10:00.000 2014-01-01 18:30 NULL NULL 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 Regards Baiju Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Mr Bindi these are sample datas
EmpId EmpName InTime OutTime dstatus whours
2500 Arun 2014-01-01 08:30 2014-01-01 09:30 NULL NULL
2500 Arun 2014-01-01 09:40 2014-01-01 11:30
2500 Arun 2014-01-01 12:30 2014-01-01 12:45
2501 Kiran 2014-01-01 14:10:00.000 2014-01-01 18:30 NULL NULL
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

Regards
Baiju

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

Posted by: Sravan661 on: 2/12/2014 [Member] Bronze | Points: 25

0	Hi, Before you insert every row to the table calculate the difference between intime and outtime and insert the value in Whours column. So, you get the Employee working hours for that particular Row (time) . do this for every row. Best way to insert values by passing first 5 parameters is by using a storedProcedure and mentions working hours logic as mention above in my post create procedure SpDailyWhours ( @empid int, @empname varchar(max), @intime datetime, @outtime datetime, @dstatus varchar(10) ) as declare @whours time = @outtime - @intime select @whours //Your insert query here After inserting so you can get total working hours of an employee per day by using below query declare @IntimeInSeconds int SELECT @IntimeInSeconds = SUM( ( DATEPART(hh, whours) * 3600 ) + ( DATEPART(mi, whours) * 60 ) + DATEPART(ss, whours) ) from Temp_EmpTimes group by EmpId,EmpName, CONVERT(VARCHAR(10),InTime,111),CONVERT(VARCHAR(10),OutTime,111) SELECT CONVERT(VARCHAR(10),@IntimeInSeconds/3600) +':' + RIGHT('00'+CONVERT(VARCHAR(2),(@IntimeInSeconds%3600)/60),2) +':' + RIGHT('00'+CONVERT(VARCHAR(2),@IntimeInSeconds%60),2) AS [HH:MM:SS] This is to get Total In time SELECT EmpId,EmpName,max(OutTime) OTime ,MIN(InTime) ITime ,convert(time, max(OutTime)-MIN(InTime)) as Emp_Intime from Temp_EmpTimes group by EmpId,EmpName, CONVERT(VARCHAR(10),InTime,111),CONVERT(VARCHAR(10),OutTime,111) fyi. . . find attachment Hope this helps you Mark as answer if you are satisfied Download source file sravan Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
Before you insert every row to the table calculate the difference between intime and outtime and insert the value in Whours column. So, you get the Employee working hours for that particular Row (time) .
do this for every row.
Best way to insert values by passing first 5 parameters is by using a storedProcedure
and mentions working hours logic as mention above in my post

create procedure SpDailyWhours

(

@empid int,

@empname  varchar(max),

@intime datetime,

@outtime datetime,

@dstatus varchar(10)

)

as

declare @whours time  = @outtime - @intime

select @whours 

//Your insert query here

After inserting so you can get total working hours of an employee per day by using below query

declare @IntimeInSeconds int

SELECT @IntimeInSeconds = SUM(

             ( DATEPART(hh, whours) * 3600 ) +

             ( DATEPART(mi, whours) * 60 ) +

               DATEPART(ss, whours)

          ) from Temp_EmpTimes 

group by EmpId,EmpName, CONVERT(VARCHAR(10),InTime,111),CONVERT(VARCHAR(10),OutTime,111)



SELECT CONVERT(VARCHAR(10),@IntimeInSeconds/3600)  

    +':' 

    + RIGHT('00'+CONVERT(VARCHAR(2),(@IntimeInSeconds%3600)/60),2) 

    +':' 

    + RIGHT('00'+CONVERT(VARCHAR(2),@IntimeInSeconds%60),2) AS [HH:MM:SS]

This is to get Total In time

SELECT EmpId,EmpName,max(OutTime) OTime ,MIN(InTime) ITime ,convert(time, max(OutTime)-MIN(InTime)) as Emp_Intime from Temp_EmpTimes 

group by EmpId,EmpName, CONVERT(VARCHAR(10),InTime,111),CONVERT(VARCHAR(10),OutTime,111)

fyi. . . find attachment
Hope this helps you
Mark as answer if you are satisfied

Download source file

sravan

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

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

0	--For your Sample data 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, DATEDIFF( MI, InTime, OutTime)/60, DATEDIFF( MI, InTime, OutTime)%60 FROM @Emp --Query for calculating total hours SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay, SUM( DATEDIFF( MI, InTime, OutTime)/60) Hrs ,SUM( DATEDIFF( MI, InTime, OutTime))%60 Mins ,RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime)/60)), 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total_hour(s)] FROM @Emp GROUP BY EmpId , EmpName, CAST(InTime AS DATE) NOTE: In the query Just replace @Emp with your TableName 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

--For your Sample data


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, DATEDIFF( MI, InTime, OutTime)/60, DATEDIFF( MI, InTime, OutTime)%60
FROM @Emp


--Query for calculating total hours 
SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay, SUM( DATEDIFF( MI, InTime, OutTime)/60) Hrs
	,SUM( DATEDIFF( MI, InTime, OutTime))%60 Mins
	,RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime)/60)), 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2)  AS [total_hour(s)]
FROM @Emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

NOTE: In the query Just replace @Emp with your TableName

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: Sravan661 on: 2/12/2014 [Member] Bronze | Points: 25

0	Hi Bandi, Good query :-) a small correction in your code the total sum (working hours) returned wrong in the above query. please note the Bolded part 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, DATEDIFF( MI, InTime, OutTime)/60, DATEDIFF( MI, InTime, OutTime)%60 FROM @Emp --Query for calculating total hours SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay, SUM( DATEDIFF( hh, InTime, OutTime)) Hrs ,SUM( DATEDIFF( MI, InTime, OutTime))%60 Mins ,RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( hh, InTime, OutTime))), 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total_hour(s)] FROM @Emp GROUP BY EmpId , EmpName, CAST(InTime AS DATE) sravan Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Bandi,
Good query :-)
a small correction in your code
the total sum (working hours) returned wrong in the above query. please note the Bolded part

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, DATEDIFF( MI, InTime, OutTime)/60, DATEDIFF( MI, InTime, OutTime)%60
FROM @Emp


--Query for calculating total hours 
SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay, SUM( DATEDIFF( hh, InTime, OutTime)) Hrs 
        ,SUM( DATEDIFF( MI, InTime, OutTime))%60 Mins
        ,RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( hh, InTime, OutTime))), 2)  + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2)  AS [total_hour(s)]
FROM @Emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

sravan

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

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

0	Hi Sravan, Oh I forgot to do FLOOR on MINUTE to HOUR calculation.. Alternate way is as follows: 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, SUM( DATEDIFF( MI, InTime, OutTime)/60) Hrs ,SUM( DATEDIFF( MI, InTime, OutTime))%60 Mins ,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)] 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

Hi Sravan,

Oh I forgot to do FLOOR on MINUTE to HOUR calculation.. Alternate way is as follows:

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, SUM( DATEDIFF( MI, InTime, OutTime)/60) Hrs
	,SUM( DATEDIFF( MI, InTime, OutTime))%60 Mins
	,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)]
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: Snaveen on: 2/13/2014 [Member] Starter | Points: 25

0	Hi, Use case Statement to achieve your goal. If time is less than the input time then return one value otherthan return some other. EX: select case when intime >8:35 and intime < 11:00 then 'L' when intime > 11:00 then 'halfday' from tablename Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	"Mark it 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

Latest Posts