calculating working hours per day for week/month for each employee

Question

calculating working hours per day for week/month for each employee

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Ah, dangit, tamer... 😉 I thought this looked familiar. Why are you double posting? All it's doing is splitting up the answers for this problem. Didn't my answer on my last post on that previous thread do what you want?

For anyone else interested, here's the location of the previous double post.

http://www.sqlservercentral.com/Forums/Topic1632995-391-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tanehome Ten Centuries Points: 1167 More actions · Answer 2

Hi Jeff,

Actually, I thought that this case is deferent as we’ve a deferent/modified table structure:ermm: moreover the previous post has been stopped and I didn’t get any other answer for my appendage reply.

Anyway, it’s better because this post is more richer and more interactive, I belive we can ignore the previous one.

And now what do you think regarding my suggestion?

tamer.h (11/22/2014)
Jeff Moden
The problem with the (First in/Last out) rule is there may ins with no outs and vice versa for individuals because of the "tailgating" problem. My question is based on that realization... what do you want to report on if that exception occurs?
If it occurs I think we have to report it as "No Out" if we have only In or "No In" if we have only out.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

Try

----Sample data

-- timeclock machines log

declare @TIME_LOG table (SNO int, nUserID int, readerid nvarchar(20), dtdatetime Datetime);

insert @TIME_LOG

select 113645, 320417,'in_a', '2014-11-2 8:21:00'

union select 113648, 320417,'out_b', '2014-11-2 9:21:00'

union select 113649, 320417,'in_a', '2014-11-3 11:41:00'

union select 113650, 320417,'out_a', '2014-11-3 16:31:00'

union select 113651, 320418,'in_b', '2014-11-2 8:30:00'

union select 113652, 320418,'out_b', '2014-11-2 9:21:00'

union select 113653, 320418,'in_a', '2014-11-2 11:27:00'

union select 113654, 320418,'in_a', '2014-11-2 13:15:00'

union select 113655, 320418,'out_a', '2014-11-2 14:22:00'

union select 113656, 320418,'out_c', '2014-11-2 14:29:00'

union select 113657, 320418,'in_a', '2014-11-3 15:00:00'

union select 113658, 320418,'out_a', '2014-11-3 16:29:00'

union select 113659, 320419,'in_c', '2014-11-2 8:29:00'

union select 113662, 320419,'out_c', '2014-11-2 16:21:00'

;

--Holidays Table

declare @Holidays table (NO int, hdate Date,ndayslong int

,hEnddate as dateadd(D,ndayslong-1,hdate));

insert @Holidays

select 1, '2014-1-1',1

union select 2, '2014-4-4',1

union select 3, '2014-5-5',1

union select 4, '2014-6-6',1

union select 5, '2014-11-4',2

--Leaves Table

declare @Leaves table (NO int, LdateStart Date,LdateEnd Date,nuserid int)

insert @Leaves

select 2, '2014-4-4','2014-4-4',320419

union select 3, '2014-5-5','2014-5-5',320419

union select 4, '2014-6-6','2014-6-6',320419

union select 5, '2014-11-3','2014-11-3',320419

-- for the month

declare @year int = 2014;

declare @month int = 11;

-- do it

declare @start date = cast(cast(@year*10000+@month*100+1 as varchar(8)) as date);

declare @end date = dateadd(D, -1, dateadd(m,1,@start));

declare @ndays int = datediff(D, @start, @end) + 1;

WITH person as (

select distinct nUserID from @TIME_LOG

where dtdatetime between @start and @end

), d31 as (

-- one may wish to use a calendar table instead of this part

select d31.n, dateadd(D, d31.n-1, @start) as dt

, case when h.NO is not null then 'W' end as Hmark

from ufn_Tally2(1,@ndays,1) as d31

left join @Holidays as h

on dateadd(D, d31.n-1, @start) between h.hdate and h.hEnddate

), daily as (

-- subject to change to detect and eliminate timeclock machines' output problems

select nUserID

, datepart(d,dtdatetime) AS dom

, CONVERT(varchar(2),DATEDIFF(mi,min(dtdatetime),max(dtdatetime))/60) + ':' + CONVERT(varchar(2)

,DATEDIFF(mi,min(dtdatetime),max(dtdatetime))%60) AS tn

from @TIME_LOG

where dtdatetime between @start and @end

group by nUserID

, datepart(d,dtdatetime)

), res as (

select p.nUserID, d31.n

, val = case when d31.Hmark is null then

case when l.NO is null then isnull(tn,'0') else 'L' end

else d31.Hmark end

from d31

full join person as p on 1=1

left join @Leaves as l

on p.nUserID = l.nuserid and d31.dt between l.LdateStart and l.LdateEnd

left join daily as d on d31.n = d.dom and p.nUserID = d.nUserID

)

select nUserID, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[31]

from res

pivot (min(val) for n in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[31]) ) as pvt

It doesn't take your new [readerid] column into account. I agree on what others have said about ensuring you have reliable timeclock machines' output as a first step. But it's rather aministrative problem, plus may be a number of scripts to detect problems and someway to amend raw output.

tanehome Ten Centuries Points: 1167 More actions · Answer 4

Thank you very mach "Serg-52", I really appreciate your assistance.