Solved

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

Posted on 2014-11-12
6
249 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
6 Comments
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
Comment Utility
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
Comment Utility
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 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now