calaculate difference between time

Posted by Klbaiju under Sql Server on 2/10/2014 | Points: 10 | Views : 1355 | Status : [Member] | Replies : 10
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down

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

Up
0
Down
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

Up
0
Down
--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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
"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

Login to post response