Need Help Updating Query for New Error Rate Thresholds

[scoutmedic]

Hi everyone,

Several months ago, I received a solution for calculating thresholds based on production and error rates, and it has worked flawlessly. Here is the solution:

Going forward, our organization will be evaluating and adjusting the allowable error rates based on data analysis. I would like to update the query to maintain existing error rates for past data while applying new thresholds to errors within the applicable date range. Below is the same data used previously, with an additional column for new acceptable error rates.

Production

Cost Ctr	Location	Date	Volume
1	Chicago	1/1/24	300
2	Tokyo	1/1/24	500
3	New York	1/1/24	200
1	Chicago	1/1/24	450
2	Tokyo	1/1/24	550
3	New York	1/1/24	175
1	Chicago	1/1/24	250
2	Tokyo	1/1/24	500
3	New York	1/1/24	300

Errors

Cost Ctr	Date	Category	Part
1	1/1/24	Broken	Wheel
1	1/1/24	Incorrect Part	Gear
2	1/1/24	Missing	Paint
2	1/1/24	Broken	Wheel
3	1/1/24	Broken	Glass
3	1/1/24	Missing	Handle
1	1/2/24	Incorrect Part	Motor
1	1/2/24	Incorrect Part	Gear
2	1/2/24	Broken	Wheel
3	1/2/24	Incorrect Part	Motor

Thresholds

Error	Part	Threshold (per 100 units) (Jan 1 - Jun 30	Threshold (per 100 units) (Jul 1 - Dec 31
Broken	Glass	0.2	0.25
Broken	Motor	0.3	0.35
Broken	Wheel	0.1	0.15
Incorrect Part	Gear	0.05	0.10
Incorrect Part	Motor	0.6	0.5
Missing	Handle	0.5	0.5
Missing	Paint	0.25	0.25
Missing	Wheel	0.3	0.3

Can anyone help update the query to reflect these changes?

Thank you!

[Anonymous]

Hi @scoutmedic 

I've understand your needs, but it's still not clear to me what's your expected result.

The first screenshot you provided was too blurry for me to see.

Please provide your expected result, It would be helpful to find out the solution.

Thanks for your cooperation!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.