November 2, 2016 at 3:53 pm
experts,
how to exclude the negative value in this OT calculation,
,CalcPart AS
(
SELECT
department,subdepartment,dutyhours,person_num, event_date, in_time, out_time, minutes,day,friday,saturday,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep
)
,tmpResult as
(
SELECT
subdepartment,--full_name,
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,
SUM(minutes) / 60.0 AS HoursWorked,
dutyhours,Day,friday,saturday,event_date,
--SUM(minutes) / 60.0 AS HoursWorked,-----This column is duplicate and could be removed
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
November 3, 2016 at 5:44 am
philand3 (11/2/2016)
experts,how to exclude the negative value in this OT calculation,
,CalcPart AS(
SELECT
department,subdepartment,dutyhours,person_num, event_date, in_time, out_time, minutes,day,friday,saturday,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep
)
,tmpResult as
(
SELECT
subdepartment,--full_name,
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,
SUM(minutes) / 60.0 AS HoursWorked,
dutyhours,Day,friday,saturday,event_date,
--SUM(minutes) / 60.0 AS HoursWorked,-----This column is duplicate and could be removed
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
Change this
CalcPart AS
(
SELECT
department,subdepartment,dutyhours,person_num, event_date, in_time, out_time, minutes,day,friday,saturday,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep
)
to this?
CalcPart AS
(
SELECT
department,subdepartment,dutyhours,person_num, event_date, in_time, out_time, minutes,day,friday,saturday,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep
WHERE minutes > 0
)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2016 at 6:30 am
philand3 (11/2/2016)
experts,how to exclude the negative value in this OT calculation,
<<removed posted query for brevity>>
This is in the category of double-posting... and hoping for an answer that doesn't disagree with existing ideas. This problem was posted elsewhere, and I've provided a number of thought-provoking questions which philand3 may have answered by now (I have yet to get back to that topic this morning), but the existence of this post (and its timing) suggests those questions may be perceived as ignorable if a "better answer" can be had elsewhere...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 3, 2016 at 6:38 am
sgmunson (11/3/2016)
philand3 (11/2/2016)
experts,how to exclude the negative value in this OT calculation,
<<removed posted query for brevity>>
This is in the category of double-posting... and hoping for an answer that doesn't disagree with existing ideas. This problem was posted elsewhere, and I've provided a number of thought-provoking questions which philand3 may have answered by now (I have yet to get back to that topic this morning), but the existence of this post (and its timing) suggests those questions may be perceived as ignorable if a "better answer" can be had elsewhere...
Thanks for the heads-up.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2016 at 8:56 am
I stated on another thread that the number of different posts for this same query indicate that the OP is purposely fragmenting the discussion to hide the complexity of the problem. When split up, each individual piece seems reasonable, but taken as a whole, it's obvious that the complexity exceeds that which can reasonably be resolved in a free forum, and that the OP needs to hire a consultant to help them with this issue.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2016 at 11:21 am
drew.allen (11/3/2016)
I stated on another thread that the number of different posts for this same query indicate that the OP is purposely fragmenting the discussion to hide the complexity of the problem. When split up, each individual piece seems reasonable, but taken as a whole, it's obvious that the complexity exceeds that which can reasonably be resolved in a free forum, and that the OP needs to hire a consultant to help them with this issue.Drew
That is indeed, a reasonable conclusion from the available information, but given the nature of folks willing to go down that road, and the details I've seen so far in this case, and the number of times I've seen this kind of thing before, ... it's far more likely that what's being hidden isn't so much the complexity of the problem, but the strong likelihood that the poster may not have any useful ability to solve the problem at all, and is hoping to use sheer number of posts to eventually find someone willing to bail him out of a mess of his own making...
I'm fairly sure from the query text posted so far, that either this individual has no understanding of the data, or has concerns about making much of the query public, but has no idea how to extrapolate from the specifics to a general case, as well as very little testing experience. Dimes to donuts says there's a good chance both are true.
Honestly, it does seem as though the query is close, but lacks the rigor that would be present if the poster really understood the nature of the existing data. I'm still not sure that what's being done with the Daypart field; that creates the additional clock-in / clock-out event pairs; is a logical choice, but I've seen nothing in the posts that indicates why that's being done, and given that the summation is taking all the event pairs and adding up the duration, I suspect that this is just the tip of the iceberg full of problems with that query. I wonder if that was an attempt to figure out how to assign either clock-in or clock-out to a solo clock event, that was "solved" in a totally wrong way, but perhaps similar to what the poster has demonstrated with the multiple postings here. If it is indeed incorrect, then the very data on which he's operating would have to be suspect, especially given the summation looks to count some of it at least twice. The problem itself intrigues me because I love to solve "data understanding" problems, as it so often richly rewards the person asking for help, and that makes me 🙂
Hey philand3 ... You've been trying to solve this problem for some time now, and keep resorting to posting separate threads to try and solve the problem piecemeal. I'm pretty darn sure that the problem is more complicated than just breaking it into pieces, and while I do recall posting that this likely needs a top-to-bottom break it into pieces analysis, that doesn't mean take the existing pieces and do that. It means take the objective and do that. I know I've been fairly critical of what you've given us so far, but if you want help, you do have to be willing to listen to new ideas, or the likelihood of solving the problem you started with tends to drop off to zero rather quickly. I'm particularly intrigued by what I've seen, but I'm continuing to see what appears to be a mad rush to find an instant solution, when every bone in my body is screaming that there's a much bigger problem that needs solving first. What say you?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 3, 2016 at 11:54 am
sgmunson (11/3/2016)
...Honestly, it does seem as though the query is close, but lacks the rigor that would be present if the poster really understood the nature of the existing data. I'm still not sure that what's being done with the Daypart field; that creates the additional clock-in / clock-out event pairs; is a logical choice, but I've seen nothing in the posts that indicates why that's being done, and given that the summation is taking all the event pairs and adding up the duration, I suspect that this is just the tip of the iceberg full of problems with that query.
That logic came from this topic, posted a couple of weeks back trying to pivot data:
http://www.sqlservercentral.com/Forums/Topic1826011-3411-1.aspx
I think part of the problem may also be a language barrier, based on this person's local time listing in their profile. They seem to be trying to use that one query to solve multiple reporting purposes, leading to the scattered fragmented postings. And to think this started out with what looked like a simple problem up front. (sigh)
November 3, 2016 at 3:07 pm
Chris,
Now that I've seen that part of it, I'm now absolutely certain that the query I was trying to work on was faulty. I'm pretty sure that the Daypart portion of things just messes things up, and it appears he originally made room for up to 4 event pairs (clock-in & clock-out) for each day, but in the query I was looking at, he only went with two, and I'm pretty sure that messes things up. With this kind of data, you really have to group things based on the earliest clock-punch in a group, and using that one, perhaps adjust to the next day if it's like 5 or 10 minutes before midnight local time, which is why converting to UTC before processing this data is a truly bad idea, as it would totally destroy any useful grouping.
Then there needs to be the pairing of the events, and then understanding the meaning of any overlapping event pairs, and what to do about them. Once that bridge is crossed, then you can order the event pairs and SUM them into the day they belong to, which is not as simple as it might seem. Then, I'd guess you need to only calculate an OT value of other than 0 if the SUM is greater than the Dutyhours figure. Then, you just might have a solution. I'm going to have to give up on the original poster, but if you post something and I think I can help, I'll be glad to take a look see...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 5, 2016 at 5:28 am
Hello sgmunson,
Thanks for trying to come out the quality answer. And thanks to chris who is continuously replied my query's without any hesitation.
I had 4 different requirement for which i m changing this query accordingly
your right that i took only 2 pair which i shown sample only but in real yes i consider more than 2 pairs not more than 3 which i am fine.
UTC i applied since my clock events records are 3 hours prior to our local time thereby i am applied UTC which is working fine,
like 5 or 10 minutes before midnight local time, its real scenario which happened since many employee do clock prior to the time start which will be usually 5 to 10 min before. the mid night and this is nothing to do with UTC time conversion which is genuine requirement.
i do not see any overlap here could you elaborate with your observation on overlap which i m might miss.
BR.
November 5, 2016 at 2:13 pm
philand3 (11/5/2016)
Hello sgmunson,Thanks for trying to come out the quality answer. And thanks to chris who is continuously replied my query's without any hesitation.
I had 4 different requirement for which i m changing this query accordingly
your right that i took only 2 pair which i shown sample only but in real yes i consider more than 2 pairs not more than 3 which i am fine.
UTC i applied since my clock events records are 3 hours prior to our local time thereby i am applied UTC which is working fine,
like 5 or 10 minutes before midnight local time, its real scenario which happened since many employee do clock prior to the time start which will be usually 5 to 10 min before. the mid night and this is nothing to do with UTC time conversion which is genuine requirement.
i do not see any overlap here could you elaborate with your observation on overlap which i m might miss.
BR.
Appreciate your response, but I try NOT to solve only HALF of a problem, or perhaps even less than half. If you want really useful help, we all need to be on the same page, which means you need to disclose exactly what a record in a given table in your query represents, along with ALL the conditions we need to take into consideration, as well as clear understanding of the desired output, and in most cases, a good understanding of WHY you need that output in that particular manner. I and most other contributors here don't have tons of time to devote to solving complex problems, so we usually try to offer simple advice and fairly simple edits at first, in the hope that merely by encountering new ideas, the original poster will come across the right solution by having learned something new. What we have seen from you so far is significant fracturing of the problem into multiple pieces, and no coherence picture of the overall problem. It's time to have YOU invest enough time to provide DDL and sample data sufficient to test the essential set of "edge cases" so as to be sure the logic of the solution actually solves it, and for you to provide the exact set of solution data that our query should produce. If you keep separating things into multiple discussion threads, problems in one part of the query may well change the conditions for other parts, and that has already happened. If you're willing to spend enough time to explain the data and your current version of query in just ONE thread, then I'll be happy to look at it again. My reason to hesitate is that I think you're not being terribly fair with your expectations. You make it appear as though you expect a brilliant solution with hardly any effort of your own to contribute. Do try to remember that we have to be able to actually see that effort, and to date, it's just not all that evident.
Now let's deal with what you just posted. Converting a time-clock punch to UTC from local time distorts reality, potentially pulling it into a different day for grouping purposes. If you're going to convert to UTC, then it has to be ALL date/time values either ALL BEFORE any kind of processing, or ALL AFTER. That's not apparent from your queries posted so far. If there's a factor I haven't considered, then you need to explain that in detail, and the reason's behind it. As to the overlap, I saw at least one post where the clock-in/clock-out pair in position 2 or 3 overlapped with the one in position one, for a given record. It may be that you've since solved that problem, or that there's some other explanation, but none of the posts that I've seen ever explained it.
Let's start fresh, shall we? A good and COMPLETE explanation of the problem, including the information that says exactly what any single record in each source table actually represents. Some idea of what the various grouping levels represent would also be helpful. I've done a fair amount of work with clock-punch data, but every implementation of such data that I've ever seen has been different from almost every other one, so a good explanation is fairly critical. I'll look forward to seeing the details, all in one place. Don't be afraid you'll miss something.... someone here is likely to end up asking...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply