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

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
Mystical_IceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
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
mlmccCommented:
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
Steve BinkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Mark WillsTopic AdvisorCommented:
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
Steve BinkCommented:
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
Aaron TomoskySD-WAN SimplifiedCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.