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

[ssoni]

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

[Rabbit]

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.

[ssoni]

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.

[Rabbit]

I can not because you never told us how the data is related and what the end results are that you're looking for.

[ssoni]

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

[Rabbit]

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.

[ssoni]

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

[Rabbit]

You've already said all that. I need you to tell me how you calculate the working hours.

[ssoni]

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.

[Rabbit]

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?