?
Solved

SQL Query - Finding discrepancy greater than or less than a percentage

Posted on 2014-11-12
6
Medium Priority
?
324 Views
Last Modified: 2014-11-24
Hi,
Here's the situation:
We have a manufacturing solution that tracks planned time (either RUN or SETUP), and actual time (whether run or setup).  The following columns are used in the tables:
ActualTime = Actual time
RunorSetup = Whether the actual time is 'run' or 'setup'
PlannedRunTime = Planned Run Time
PlannedSetupTime = Planned Setup Time
CompletedQty = Whether the operation is 'completed' or not.

The purpose of the completed qty, is often the operation is still in progress, and if there is a discrepancy where the ACTUAL hours worked are less than the PLANNED hours worked, it's probably because it's still in progress, so we don't care to show it.  However, if the completed qty is '1', that means the part has been finished, and in that case we do care if the planned vs actual difference is more than a %.

There is one user-entered variable, and that's "@Percentage".  What the below query should be (in theory) telling me, is
what percentage discrepancy exists between Plan and Actual.  For instance, if a part was supposed to take 10 hours to complete, and it ended up taking 9 hours to complete, we might not care.  If a part was supposed to take 10 and took 3, then we would.  The purpose of the @Percentage input is for the user running the report to determine what they want to see.  So if the part was supposed to take 10 hours (planned) and it ended up taking 15 hours (actual), if the user sets the percentage discrepancy to 75%, then it theoretically shouldn't show up.  If the user sets the percentage discrepancy to 20%, then it should.  Hopefully that's making sense.

This is the query I have at the moment:

Where
((CAST(X.[ActualTime] AS DECIMAL(18,2)) > 
(Case When X.[RunorSetup] = 'Run'
Then CAST(X.[PlannedRunTime] AS DECIMAL(18,2)) + (@Percentage * CAST(X.[PlannedRunTime] AS DECIMAL(18,2)))
ELSE CAST(X.[PlannedSetupTime] AS DECIMAL(18,2)) + (@Percentage * CAST(X.[PlannedSetupTime] AS DECIMAL(18,2))) END ) )
OR
(CAST(X.[ActualTime] AS DECIMAL(18,2)) <=
CASE WHEN X.[CompletedQty] = 1 THEN
      (Case When X.[RunorSetup] = 'Run'
      Then CAST(X.[PlannedRunTime] AS DECIMAL(18,2)) - (@Percentage * CAST(X.[PlannedRunTime] AS DECIMAL(18,2)))
      ELSE CAST(X.[PlannedSetupTime] AS DECIMAL(18,2)) - (@Percentage * CAST(X.[PlannedSetupTime] AS DECIMAL(18,2))) END )
      ELSE CAST(X.[ActualTime] AS DECIMAL(18,2))
      END))      


FOR SOME REASON, on the above script, when the 'Actual' time is '0', if the planned time has ANY amount of time, and the @Percentage is 100% or above, those '0' actual time operations don't show up.  

I'm not sure what I'd need to change to make them show up.  To me, if a part has 100 planned hours, and 0 actual hours, even if the user enters in 100000%, it should still show up.

Open to any suggestions here
0
Comment
Question by:Mystical_Ice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 400 total points
ID: 40439814
The way I read it, if the Actual time is 0 ...

 The first test (>) will only be true if the % is "more" than -100% (eg. -101%).  Then the result of the Run/Setup time calculation will be less than 0 (so an Actual time of 0 will be >).

 The second test (<=) will only be true if CompletedQty is not 1, or if the % is 100% or less.  If the % is higher (eg. 101%), then the result of the Run/Setup time calculation will be negative (meaning that it will be less than the Actual time of 0).

 If you want an Actual time of 0 to be a special case, the simplest thing might be to add another test for that.  Something like:

OR
(CAST(X.[ActualTime] AS DECIMAL(18,2)) = 0 AND
X.[CompletedQty] = 1 AND
(Case When X.[RunorSetup] = 'Run'
Then CAST(X.[PlannedRunTime] AS DECIMAL(18,2))
ELSE CAST(X.[PlannedSetupTime] AS DECIMAL(18,2))
END) <> 0)

Open in new window


 If you also want rows where the Actual time is 0 and CompletedQty is not 1, you could add that.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40439933
Since you have the entire test in ( ), is this part of a larger where clause?

If so could that part be causing the records to not be selected?

mlmcc
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1200 total points
ID: 40440179
So, something like this:
WHERE
    CompletedQty = 1
    AND ( 
        ABS(
            CAST(
                (CASE RunorSetup WHEN 'RUN' THEN PlannedRunTime
                ELSE PlannedSetupTime END) AS DECIMAL(18,2)
            ) - CAST(ActualTime AS DECIMAL(18,2))
        ) >= ( 
            CAST(
                (CASE RunorSetup WHEN 'RUN' THEN PlannedRunTime
                ELSE PlannedSetupTime END) AS DECIMAL(18,2)
            ) * @Percentage )

Open in new window


Now would be a good time to point out that if you need those values to be DECIMAL(18,2), it might be best to store them as such.  That would eliminate all the CASTing you have in the current query.

Also, since you already have a flag to indicate 'run' or 'setup', you really only need the single time field - the flag will provide the context.  That would remove the necessity of the CASE function:
WHERE
    CompletedQty = 1
    AND ABS(PlannedTime - ActualTime) >= (PlannedTime * @Percentage)

Open in new window

0
Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 400 total points
ID: 40441107
The second part of the "OR" will probably / expected to always return a result.....

Well, I am guessing that was the intent behind the bottom line assumption was something like :

CAST(X.[ActualTime] AS DECIMAL(18,2) >  somevalue
OR
CAST(X.[ActualTime] AS DECIMAL(18,2)) <=  case  where completed then somevalue ELSE CAST(X.[ActualTime] AS DECIMAL(18,2))

that final "else" combined with "<=" will (assume) hopefully include results where [ActualTime] = [ActualTime] even when zero...  

Now, lets consider the "somevalue"...  Seems to be a rather consistent calculation based on (with plus or minus) :

Case When X.[RunorSetup] = 'Run'
       Then CAST(X.[PlannedRunTime] AS DECIMAL(18,2)) - (@Percentage * CAST(X.[PlannedRunTime] AS DECIMAL(18,2)))
       ELSE CAST(X.[PlannedSetupTime] AS DECIMAL(18,2)) - (@Percentage * CAST(X.[PlannedSetupTime] AS DECIMAL(18,2)))
END

let us assume the maths is correct for the above condition of 'Run' being if it is 'Run' then use Runtime else use Setuptime.

Now consider your paradox of To me, if a part has 100 planned hours, and 0 actual hours, even if the user enters in 100000%, it should still show up.

In that scenario, with zero actual, we would assume that 'completedqty' is not '1'... So, we are left with the dilemma of not checking the correct "somevalue" if not completed and actual hours is not ">"

Hmmmm, Apart from not knowing which "planned" you refer to...

Sounds like the conditions aren't going to be met (too many occurrences of the word "not" in the previous comment, combined with "or" always makes it tricky and signals a rethink).

So whataboutif we change it around a bit and then check the various conditions....

CASE somevalue
        when completed
        when  < actual
        when  >= actual
        else some_other_condition
END

Now a neat trick with such a list of conditions is to compute a true / false (or even an exposed 1 or 0) scenario in your "where" clause... e.g.

WHERE 1 =
    (CASE somevalue
            when completedqty = 1 and whatever-else then 1
            when  < actual then 1
            when  >= actual then 1
            when other_conditions-to-check then 1
            else 0
     END)

When laid out in priority of execution within the CASE, it will exit the case when the condition exists, bypassing the rest of conditions to check within the case statement.

As for "somevalue" in the above, ideally, you run your select as a CTE (or subquery) so you can name your calculation and use that name in your WHERE clause. And make it a variance so you don't have to worry as much about the plus or minus (depending on completed)

I can try to build a sample if you want greater clarity....
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 40441479
If you examine my response, you'll see that those issues are addressed in my answer.
The first predicate checks the completed status, so only items marked as completed will appear in the results.  This allows for the possibility of a "catastrophic failure" scenario where the item is completed but no time was spent.
Over/under tolerances are checked by way of the call to ABS(), which consolidates the logic into a single test
If you did want to make sure actual time was non-zero, it would be as simple as adding another predicate to the WHERE clause: CAST(ActualTime AS DECIMAL(18,2)) > 0
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 40450545
Agreed that the where has gotten a bit much and a CTE (or subquery if you really want) could help to clean it up. When you spit out a calculated column in a cte, then the query can use the column in a where without having to do that math all over again.

I would suggest doing the discrepency calculations all in a cte, then the where for the output would simply be
where discrepency >= @percentage
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question