Solved

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

Posted on 2014-11-12
6
259 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
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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 38

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

920 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

13 Experts available now in Live!

Get 1:1 Help Now