Solved

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

Posted on 2014-11-12
6
286 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 100 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 100

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 50

Accepted Solution

by:
Steve Bink earned 300 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 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 50

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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