423,682 Members | 1,348 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

how to fetch total working hours from the table for every employee for a week

P: 9
hi,
I am new to sq server, and
I want to calculate the total working hours for all the employees excluding their break. This is the sample table for 2 employes.

logID logDate logTime srno

2 12/19/2011 0:00 1899-12-30 16:36:16.000 933252
2 12/19/2011 0:00 1899-12-30 11:21:20.000 933137
2 12/20/2011 0:00 1899-12-30 15:55:33.000 933636
2 12/20/2011 0:00 1899-12-30 15:55:18.000 933635
2 12/20/2011 0:00 1899-12-30 12:18:25.000 933539
2 12/22/2011 0:00 1899-12-30 16:07:25.000 934472
2 12/22/2011 0:00 1899-12-30 10:47:49.000 934361
2 12/23/2011 0:00 1899-12-30 16:00:55.000 934841
2 12/23/2011 0:00 1899-12-30 11:02:04.000 934734
6 12/19/2011 0:00 1899-12-30 21:15:33.000 933350
6 12/19/2011 0:00 1899-12-30 19:09:05.000 933308
6 12/19/2011 0:00 1899-12-30 18:45:04.000 933298
6 12/19/2011 0:00 1899-12-30 18:43:33.000 933297
6 12/19/2011 0:00 1899-12-30 16:51:43.000 933261
6 12/19/2011 0:00 1899-12-30 16:15:37.000 933244
6 12/19/2011 0:00 1899-12-30 13:57:20.000 933205
6 12/19/2011 0:00 1899-12-30 13:42:36.000 933196
6 12/19/2011 0:00 1899-12-30 12:17:06.000 933149
6 12/20/2011 0:00 1899-12-30 18:55:45.000 933704
6 12/20/2011 0:00 1899-12-30 16:36:05.000 933648
6 12/20/2011 0:00 1899-12-30 14:09:59.000 933601
6 12/20/2011 0:00 1899-12-30 13:41:49.000 933588
6 12/20/2011 0:00 1899-12-30 12:33:03.000 933543
6 12/21/2011 0:00 1899-12-30 18:40:58.000 934110


Thanks in advance
Jan 31 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,272
Why is date separated from time? They should be together. I have no idea how the records are related to each other, so there's no where enough information to suggest a solution.
Jan 31 '12 #2

P: 9
hi,
I know, but only this database is available to me. I have to work on it. Can you suggest any cursor program or something.

Thanks.
Feb 1 '12 #3

Rabbit
Expert Mod 10K+
P: 12,272
I can not because you never told us how the data is related and what the end results are that you're looking for.
Feb 1 '12 #4

P: 9
That is one table stored in SQL SERVER and I have to use that to find the total working hour for each employee for one week.

I have written a SP for that but it was showing inconsistent result.
I have taken date part from first datetime column and timepart from other datetime column.

The SP is as follows-
create procedure sp_workhours
as
begin
declare @logID int
declare @logDate datetime
declare @logTime datetime
declare @srno int
declare @count int
declare @checkID int
declare @workhrs int =0
declare @fromDate datetime
declare @toDate datetime
set @count=0

/*declare cursor to calculate total working hours*/
declare workhours cursor for
select * from logtable where logDate between '2011-12-19' and '2011-12-23' order by logID, logDate, logTime;
open workhours;

fetch workhours into
@logID,@logDate,@logTime,@srno

WHILE @@FETCH_STATUS = 0
begin
set @count=@count+1

if(@checkID = @logID or @count = 1)
begin
if((@count%2=1 or @count = 1) )
begin

set @checkID = @logID
set @fromDate = convert(datetime,(CONVERT(varchar(11), @logDate, 112)+' ' +CONVERT(VARCHAR(20), @logTime, 114)))
print 'from'
print @fromDate

end
else if(@count%2=0)
begin
set @toDate = convert(datetime,(CONVERT(varchar(11), @logDate, 112)+' ' +CONVERT(VARCHAR(20), @logTime, 114)))
print @toDate


if( DATEDIFF(day ,@fromDate, @toDate)=0 )
begin

set @workhrs = @workhrs + DATEDIFF(SECOND ,@fromDate, @toDate)
end

else
begin
print 'notmath'
print 'todate'
print @toDate
set @fromDate =null
set @toDate = null
end

print @workhrs/3600
end
end
else
begin

declare @ResTable Table
(
logID int,
workhrs float
)
insert into @ResTable (logID,workhrs) values(@checkID ,@workhrs/3600 )


-- select logID, from logtable order by logDate,logID
set @count =0
set @workhrs=0
end

fetch next from workhours into
@logID,@logDate,@logTime,@srno
end
close workhours
select * from @ResTable

end
Feb 1 '12 #5

Rabbit
Expert Mod 10K+
P: 12,272
You've already said you're trying to get working hours. That still tells me nothing about how the records are related to each other nor does it tell me what the end results should look like.
Feb 1 '12 #6

P: 9
I'm trying to get the no. of hours each employee worked for the complete week. In the end result i want employee and his total working hour. The Date and Time values are related to each other.

thanks
Feb 2 '12 #7

Rabbit
Expert Mod 10K+
P: 12,272
You've already said all that. I need you to tell me how you calculate the working hours.
Feb 2 '12 #8

P: 9
ok. i try to take the date value from the first date time col. and time value from other col. and take the diff of every two even and odd time value.
here, now want that if the time value has not other matching even value of time it must be skipped on the same date. and it again start new calculation for new date. that is how it calculate the working hour for the week.

i m not sure whether my logic is correct.
but that is what i want to do.

thanks.
Feb 2 '12 #9

Rabbit
Expert Mod 10K+
P: 12,272
What does that even mean? What is an "even" time value? What is an "odd" time value? What is a "matching even value of time"?

What does the data in the table even represent?
Feb 2 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.