Project: Evaluate a Manufacturing Process

Enhancing Quality Control in Manufacturing: Applying Statistical Process Control with SQL

The project on "Evaluating a Manufacturing Process" served as a training exercise to showcase my ability to decompose a complex project into manageable parts and apply rigorous analytical techniques to ensure quality control in manufacturing. A significant component of this initiative was my decision to utilise ChatGPT as an assistant for the challenging task of applying SQL window functions in the data analysis phase.

In this project, my main objective was to support a team aiming to enhance how they monitor and control their manufacturing process, by establishing statistical boundaries for product quality using Statistical Process Control (SPC). The acceptable range for the height of manufactured parts was defined by an upper control limit (UCL) and a lower control limit (LCL), calculated with specific formulas that incorporated statistical variations to maintain product consistency and quality.

ChatGPT played an important role in helping me understand and implement the SQL queries required to calculate rolling averages and standard deviations using window functions. The interactive guidance provided by ChatGPT enabled me to break down the steps involved in analysing historical manufacturing data to identify any deviations that fell outside the defined control limits.

1.1 Checking for Null values

There were no Null values in the dataset

1.2 Implementing Sequential Data Ordering

In the preliminary phase of the data analysis for the manufacturing process evaluation project, I applied the SQL ROW_NUMBER() window function to the 'manufacturing_parts dataset.' This function was used to assign a unique sequential integer to rows within partitions of the dataset. The data was partitioned by the 'operator' column, indicating that each group of rows processed by the same operator received a distinct sequence of numbers, starting from one. Additionally, within each partition, the rows were ordered by the 'item_no' to maintain a logical order of the parts handled.

This step was crucial for understanding the flow of items through the manufacturing process, handled by different operators. It would allow the team to trace the order of operations and ensure that subsequent data manipulations or analyses, such as calculating rolling averages or identifying outliers, would be accurately informed by the correct sequence of data entry.

This structured approach not only provided clarity in tracking the progress of each operator's output but also set the foundation for deeper analytical tasks aimed at enhancing quality control and efficiency in the manufacturing process."

1.3 Calculating Moving Averages and Standard Deviations

I used the AVG() window function partitioned by the operator and ordered by item_no. This function calculates the average height over a window defined by the four preceding rows and the current row (ROWS BETWEEN 4 PRECEDING AND CURRENT ROW). This moving average helps in identifying trends over a fixed number of observations, smoothing out short-term fluctuations and highlighting longer-term trends in height measurements.

Similarly, I employed the STDEV() window function with the same partitioning and ordering. This function computes the standard deviation of the height measurements over the same window used for the average. The standard deviation provides insights into the variability of the part dimensions that each operator is producing, which is essential for quality control and ensuring that the manufacturing process remains consistent.

These calculations are essential for Statistical Process Control (SPC), a method used to monitor and control a process through the use of statistical methods. By understanding both the average performance and variability of the process, the team can ensure that the process remains stable and within specified control limits, reducing the risk of defects.

Focusing on the visualisation of data for Operator 20 (chosen randomnly) provided a tangible context to the abstract nature of the manufacturing dataset. The detailed examination helped demystify the complexities of the data, transforming raw numbers into actionable insights. Through this process, I learned the importance of targeted analysis in making data more interpretable and meaningful, which is essential for effective communication and decision-making in a business environment.

From the analysis of Operator 20's data I can see that the variation in part heights, indicated by the changing standard deviations, highlighted where process control measures could be reinforced. Additionally, observing the trends in the moving averages helped identify whether the operator's performance remained stable or showed fluctuations over time. However, to assess whether the fluctuations in Operator 20's data are excessive, I would need to compare the observed standard deviations and trends against manufacturing norms and product specifications, information which I do not have.