November 20, 2014 at 5:29 am
Hi All,
here is a table shows the dates and times of entry and exit of staff throughout the day, where each record shows that either employee's entry or exit (check in or out)
based on (first in/last out) rule, i need to calculate the working hours (difference between first and last timing per day) per each employee.
declare @TIME_LOG table (SNO int, nUserID int, readerid as nvarchar, 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'
select nUserID,
CONVERT(varchar(2),DATEDIFF(mi,min(dtdatetime),max(dtdatetime))/60) + ':' + CONVERT(varchar(2),DATEDIFF(mi,min(dtdatetime),max(dtdatetime))%60)AS n
from @TIME_LOG --where monthyear='2014-10' and nUserID='1375'
group by nUserID, RIGHT('0'+CAST(DAY(dtdatetime) AS varchar(2)),2)
_______________________________________________________________________
- there is a holiday table contains list of holidays.
- "L" means on leave and "W" means weekend otherwise user will be absent "0"
- Friday is weekend
--Holidays Table
declare @Holidays table (NO int, hdate Date,ndayslong int)
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
would any one assist me how can i calculate total working hour for each employee per day for one week and/or for one month as per below
thank you.
_______________________________________________________________________
UserID [1] [2] [3] [4] [5] [6] [7]......................[29] [30] [31]
----------------------------------------------------------------------------------------------------
320417 1:0 4:50 W
320418 5:59 1:29 W
320419 7:52 L W
:w00t:
November 20, 2014 at 7:15 am
Not certain on 'L' ,'0' logic.
For a given month
declare @TIME_LOG table (SNO int, nUserID int, dtdatetime Datetime)
insert @TIME_LOG
select 113645, 320417, '2014-11-2 8:21:00'
union select 113648, 320417, '2014-11-2 9:21:00'
union select 113649, 320417, '2014-11-3 11:41:00'
union select 113650, 320417, '2014-11-3 16:31:00'
union select 113651, 320418, '2014-11-2 8:30:00'
union select 113652, 320418, '2014-11-2 9:21:00'
union select 113653, 320418, '2014-11-2 11:27:00'
union select 113654, 320418, '2014-11-2 13:15:00'
union select 113655, 320418, '2014-11-2 14:22:00'
union select 113656, 320418, '2014-11-2 14:29:00'
union select 113657, 320418, '2014-11-3 15:00:00'
union select 113658, 320418, '2014-11-3 16:29:00'
union select 113659, 320419, '2014-11-2 8:29:00'
union select 113662, 320419, '2014-11-2 16:21:00'
;
declare @year int = 2014;
declare @month int = 11;
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
), daily as (
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 monthyear='2014-10' and nUserID='1375'
where dtdatetime between @start and @end
group by nUserID
, datepart(d,dtdatetime)
), res as (
select p.nUserID, d31.n
, val = case datename(dw,dateadd(D, d31.n-1, @start)) when 'Friday' then 'W' else isnull(tn,'0') end
from ufn_Tally2(1,@ndays,1) as d31
full join person as p on 1=1
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
Adjust holidays and weekends as needed. Use tally table or function of your choice, or use a calendar table instead.
November 20, 2014 at 7:34 am
serg-52 (11/20/2014)
You posted code that contained a function ufn_Tally2 but you didn't post it. Do you really have a function that creates a tally table on the fly? Seems like it would be more efficient to just create a base table and persist it. Can you share that function with us?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2014 at 7:54 am
first, thank you very much for your reply.:-)
sorry i didn't understand what do you mean by:
"Not certain on 'L' ,'0' logic.
For a given month"
and would you please explain what is the "ufn_Tally2" ?
November 20, 2014 at 10:05 am
tamer.h (11/20/2014)
based on (first in/last out) rule, i need to calculate the working hours (difference between first and last timing per day) per each employee.
Ok, so what do you want to do if the employee works through midnight? What do you want to do about missing data such as a person that has an "IN" time with no "Out" time for a given day? And where is the indication of "IN" or "OUT" in the data. Without that information, there's no way to properly resolve the problems I just mentioned.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2014 at 1:16 pm
Ok, so what do you want to do if the employee works through midnight?
really I don't have any idea, what is your suggestion?
What do you want to do about missing data such as a person that has an "IN" time with no "Out" time for a given day? And where is the indication of "IN" or "OUT" in the data. Without that information, there's no way to properly resolve the problems I just mentioned.
actually, there was indication for "IN" and "OUT" i didn't mentioned it thought it's useless as we only will consider the first and last time.
i think if user have only one time either "in" or "out" we can consider it as "Missing in" or "missing out"
please correct me...
November 20, 2014 at 1:46 pm
You can't calculate "work hours" without "in" and "out" markers. The work time calc is very easy if you a matching "out" for every "in".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2014 at 3:36 pm
tamer.h (11/20/2014)
Ok, so what do you want to do if the employee works through midnight?
really I don't have any idea, what is your suggestion?
What do you want to do about missing data such as a person that has an "IN" time with no "Out" time for a given day? And where is the indication of "IN" or "OUT" in the data. Without that information, there's no way to properly resolve the problems I just mentioned.
actually, there was indication for "IN" and "OUT" i didn't mentioned it thought it's useless as we only will consider the first and last time.
i think if user have only one time either "in" or "out" we can consider it as "Missing in" or "missing out"
please correct me...
If the user only has an "in" time on one day and an "out" time the next day, they may be a part time worker that worked through midnight (just as an example).
My recommendation would be to repost some data you previously posted but include the "in/out" indicators provided by the timeclock machines.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2014 at 2:10 am
Sean Lange (11/20/2014)
serg-52 (11/20/2014)
You posted code that contained a function ufn_Tally2 but you didn't post it. Do you really have a function that creates a tally table on the fly? Seems like it would be more efficient to just create a base table and persist it. Can you share that function with us?
Yes, certainly. Below is a function i use in sandbox mostly. For the tasks like this most efficient way could be using calendar table, i think.
CREATE FUNCTION [dbo].[ufn_Tally2](
@pStartValue bigint= 1,
@pEndValue bigint= 1000000,
@pIncrement bigint= 1
)
RETURNS TABLE
AS
-- quick dynamic tally table
return(
with L0(N) as (-- 10**2
select top(100) null from sys.all_objects
),L1(N) as (-- 10**2**2
select null
from L0 a1
cross join L0 a2
),L2(N) as (-- 10**2**2**2
select null
from L1 a1
cross join L1 a2
)
select top ((abs(case when @pStartValue < @pEndValue
then @pEndValue
else @pStartValue
end -
case when @pStartValue < @pEndValue
then @pStartValue
else @pEndValue
end))/abs(@pIncrement) + 1)
N = @pStartValue + @pIncrement *(row_number() over(order by a1.N)-1)
from L2 a1
cross join L2 a2
);
November 21, 2014 at 2:20 am
tamer.h (11/20/2014)
first, thank you very much for your reply.:-)sorry i didn't understand what do you mean by:
"Not certain on 'L' ,'0' logic.
For a given month"
and would you please explain what is the "ufn_Tally2" ?
How do you want 'L' or '0' marks to be calculated? I couldn't find it in your post. So the script above calculates only '0'.
The script is intended to produce a row for a month specified by the variables
declare @year int = 2014;
declare @month int = 11;
November 21, 2014 at 11:09 am
thank you all for assistance and support.
--Jeff Moden
If the user only has an "in" time on one day and an "out" time the next day, they may be a part time worker that worked through midnight (just as an example).
My recommendation would be to repost some data you previously posted but include the "in/out" indicators provided by the timeclock machines.
I've modified my post by adding column shows the "in/out" indicators provided by the timeclock machines.
sometimes many users have two check outs or two checkins, it happens during the day when two users pass together from the gate and they using one card only.
serg-52
How do you want 'L' or '0' marks to be calculated? I couldn't find it in your post. So the script above calculates only '0'.
The script is intended to produce a row for a month specified by the variables
declare @year int = 2014;
declare @month int = 11;
thank you for providing the function.
I've added the two tables for Holidays and leaves in my post above, can we make it to show "L" or "H"
on the mentioned day for the employees?
November 21, 2014 at 5:52 pm
tamer.h (11/21/2014)
sometimes many users have two check outs or two checkins, it happens during the day when two users pass together from the gate and they using one card only.
Such "tailgating" is going to make things really tough on accuracy. What do you want to report if it appears that has happened? For example, do you want a separate report of unresolvable clock entries?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2014 at 12:55 am
Jeff Moden (11/21/2014)
tamer.h (11/21/2014)
sometimes many users have two check outs or two checkins, it happens during the day when two users pass together from the gate and they using one card only.Such "tailgating" is going to make things really tough on accuracy. What do you want to report if it appears that has happened? For example, do you want a separate report of unresolvable clock entries?
yes, that's why we are using (First in/Last out) rule.
If it has happened we'll consider the difference between the first and the last timing, I think the main problem is if we have only one timing entry and my suggest is to mentioned it same as "L" for "Leaves" and "W" for weekend, for example "No Out" or "No In"...what do you think?
November 22, 2014 at 1:56 am
tamer.h (11/22/2014)
Jeff Moden (11/21/2014)
tamer.h (11/21/2014)
sometimes many users have two check outs or two checkins, it happens during the day when two users pass together from the gate and they using one card only.Such "tailgating" is going to make things really tough on accuracy. What do you want to report if it appears that has happened? For example, do you want a separate report of unresolvable clock entries?
yes, that's why we are using (First in/Last out) rule.
If it has happened we'll consider the difference between the first and the last timing, I think the main problem is if we have only one timing entry and my suggest is to mentioned it same as "L" for "Leaves" and "W" for weekend, for example "No Out" or "No In"...what do you think?
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?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2014 at 3:40 am
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.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply