Total Sum is not Calculating in event of Multiple Clock events
-
Dear Experts,
OT sum is not computing correctly in case of multiple clock events, find below my sql code, example. image marked Hoursworked "7.31" duty hours "8" = OT shouldbe "-1.31" where as its showing -8.68
WITH Prep AS(SELECT ci.action_id,EMAIL_address1 as Sub_Department,friday,saturday,preffered_name as Department,government_num as Dutyhours,ci.person_num, ci.full_name, person_id,ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,DATEname(weekday,dbo.UtcToLocal(co.creation_date)) AS Day,
CAST(dbo.UtcToLocal(ci.creation_date) AS date) AS in_date, CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time,
CAST(dbo.UtcToLocal(co.creation_date) AS date) AS out_date, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,
DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
FROM TEST ci
OUTER APPLY (SELECT TOP 1 creation_date FROM TEST t WHERE t.person_num = ci.person_num AND t.creation_date >= ci.creation_date AND t.event_name = 'CLOCK_OUT' AND ci.action_id = ci.action_id ORDER BY t.creation_date) co
WHERE ci.event_name = 'CLOCK_IN'
AND preffered_name='FINANCE'),
CalcPart AS
(SELECT action_id,day,friday,saturday,Dutyhours, person_id,Sub_Department, Department,person_num, full_name, event_date, in_time, out_time, in_date, out_date,minutes,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep)
SELECT Department,event_date,person_num,full_name, --full_name,
MAX(CASE WHEN daypart = 1 THEN in_date END) AS DATE_IN1,
MAX(CASE WHEN daypart = 1 THEN out_date END) AS DATE_OUT1,
MAX(CASE WHEN daypart = 1 THEN in_time END) AS CLOCK_IN1,
MAX(CASE WHEN daypart = 1 THEN out_time END) AS CLOCK_OUT1,
MAX(CASE WHEN daypart = 2 THEN in_time END) AS CLOCK_IN2,
MAX(CASE WHEN daypart = 2 THEN out_time END) AS CLOCK_OUT2,
MAX(CASE WHEN daypart = 3 THEN in_time END) AS CLOCK_IN3,
MAX(CASE WHEN daypart = 3 THEN out_time END) AS CLOCK_OUt3,
day,friday,saturday,
SUM(minutes) / 60.0 AS HoursWorked,
dutyhours,
case when ( [friday] = 1 and [day] = 'Friday' )
or ( [saturday] = 1 and [day] = 'Saturday' )
then SUM(minutes) / 60.0
else sum(( [minutes] /60.0) - [Dutyhours] )
end as OT
FROM CalcPart
GROUP BY Department,Day,friday,saturday,dutyhours,event_date, full_name,person_num
-
Without sample data, we can only guess at the outcome.
Try this for your OT calculation
CASE WHEN ( [friday] = 1 and [day] = 'Friday' )
OR ( [saturday] = 1 and [day] = 'Saturday' )
THEN SUM([minutes]) /60.0
ELSE SUM([minutes]) /60.0 - [Dutyhours]
END AS OT
-
Hi,
My issue is different. if you see where multiple clock ex: clock in1, clock out1,clock in2,clock out2, and see the OT which is not computing correctly since this has more than than 2 clock events.
-
Here's your view definition in a format that makes it much easier to read. But we're also going to need table DDL and sample data for all tables, please.
CREATE VIEW dbo.TESTAS
SELECT
a.action_id
,p.person_id
,a.creation_date
,p.preffered_name
,p.government_num
,h.badge_num
,a.terminal_name
,a.event_name
,p.person_num
,a.company_code
,a.terminal_id
,a.timezone
,h.event_name AS Expr15
,p.first_name
,p.full_name
,p.rcd_num AS Expr17
,p.badge_enabled
,p.keyboard_enabled
,p.shiftnum
,s.shiftvalue
,p.last_name
,p.email_address1
,p.email_address2
,s.holiday
,s.friday
,s.saturday
FROM erp.person p
FULL OUTER JOIN dbo.shiftdefination s ON p.person_id = s.person_id
INNER JOIN erp.action_history h ON p.person_id = h.person_id
INNER JOIN erp.action a ON a.action_id = h.action_id
GO
John
-
Hi,
For Data Definition Language please refer my first page.
BR
-
philand3 (11/1/2016)
Hi,For Data Definition Language please refer my first page.
BR
I think people are asking for the create table statement of your erp.action and erp.action_history tables since that is what you provided sample data for. Also, you provided sample data for 00010361 and 00010371 but your wrong calculations were for person_num 00010272
-
Hello chris,
Thanks for your feedback.Could you direct the reason for not computing OT correctly in event of multiple clock events ?
Many thanks in Advance.
-
We can't without seeing the data for person_num 00010272 which is the one you said is calculating OT wrong.
-
OK, wait... maybe this line is the problem:
else sum(( [minutes] /60.0) - [Dutyhours] )should be:
else sum(( [minutes] /60.0)) - [Dutyhours] -
Hi,
Data is given for also 10371 for which find below query results,
-
Chris it worked.
Thank you..
-
Would it be possible to get DDL (CREATE TABLE statement) for the table(s), some sample data representative of the problem domain (as INSERT INTO statements) for the table(s), and expected results based on the sample data?
Trying to write code with nothing but pictures of data doesn't always work.
-
While this appears to be "fixed"... I have to ask why an hour is being added to the creation date to get the event date?
See this part of the original query posted:
CAST(DATEADD(HOUR, 1, dbo.UtcToLocal(ci.creation_date)) AS date) AS event_date,
Just seems that it could really throw things off if someone came in at 11:00 pm local time and worked only 45 minutes before, say, they get sick, or get injured, or are working off-hours remotely and only need to do something over a short time-period. The event date would no longer match the actual date that was worked. Of course, without any actual business rules to describe the data and only pictures of data, it's kind of hard to know what to think... I also have to wonder how some of the clock in and clock out value pairs shown in some of those pictures manage to do things like overlap, or not be in sequence with daypart 1 being after daypart 2, or other similar things... I'm not sure I'd trust a system where the data looks like that...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
sgmunson (11/2/2016)
While this appears to be "fixed"... I have to ask why an hour is being added to the creation date to get the event date?This is from another question he had in the forums, which he didn't explain here. There were people that had a shift that started at midnight, and would clock in before midnight, so it wasn't grouping the multiple clock in/clock out events to the same day unless the event date of the clock in was calculated.
-
Chris Harshman (11/2/2016)
sgmunson (11/2/2016)
While this appears to be "fixed"... I have to ask why an hour is being added to the creation date to get the event date?This is from another question he had in the forums, which he didn't explain here. There were people that had a shift that started at midnight, and would clock in before midnight, so it wasn't grouping the multiple clock in/clock out events to the same day unless the event date of the clock in was calculated.
Okay, but without calculation, based on some specific set of conditions, how would he be able to tell the difference between that scenario and some remote worker performing a late-night maintenance task at any time after 11:00 PM on any given day? He's also subtracting the Dutyhours field from certain summed observations, and I'm pretty sure that summing two (or more) observations that appear to be the exact same person, and then subtracting just 8 duty hours from whatever sum results, is causing the negative values because I'm not sure that he's really thought the process through. Seems to me that each and every clock-in / clock-out event pair duration should be compared with Dutyhours, and only if that duration is longer than Dutyhours, should there be an OT value. At least that appears to be what he's trying to accomplish, but instead, he just subtracts it no matter how many event pairs go into the summation. Add to that his willingness to double-post on this (he has another post just looking for a query solution to what is clearly a design problem), and I suspect this person is either "in over their head", or won't let go of the ego attached to his existing solution. I have yet to see any viable explanation of exactly what the various data elements represent, so that someone trying to help him solve the problem can actually do so in a relatively short period of time. Instead, the problem just drags on and on...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply