Worker Efficiency Data Set

Overview

The primary purpose of the stored procedure [dbo].[uspCalculateWorkerEfficiency] is to evaluate and calculate the efficiency of workers based on scanning data collected during production.

By analyzing the number of pieces produced, their corresponding Standard Minute Values (SMV), and login times, this procedure provides insights into worker performance and operational productivity.

The procedure operates in a structured manner:

1. Temporary Table Management:

   Checks if the temporary tables (#ScanningInfo and #EfficiencyDataSet) exist and drops it if they do.

2. Scanning Data Aggregation:

   Using a Common Table Expression (CTE), it aggregates data from the TodayPieceWiseScanMasterDN table.

   This aggregation includes calculating the total number of pieces produced by each worker and the total earned minutes based on their SMV.

3. Worker Login Data Integration:

   The procedure joins the aggregated scanning data with worker login information from the WorkerScanMaster table and retrieves relevant line details.

   This integration enables the calculation of worker efficiency in the context of their operational environment.

4. Final Data Storage:

   After processing, the aggregated efficiency data is inserted into the permanent table WorkerEfficiencyDataSet, making it readily available for reporting and analysis.

5. Cleanup:

   Finally, the procedure ensures that temporary tables are dropped to free up resources and maintain a clean database environment.

This structured approach ensures accurate calculation and efficient storage of worker efficiency metrics, which can be used for performance evaluations, operational improvements, and decision-making in the organization.

Dependencies

The execution of the stored procedure [dbo].[uspCalculateWorkerEfficiency] relies on the following database objects:

1. Tables:

   • [Data].[TodayPieceWiseScanMasterDN]: Contains the piece-wise scanning data used to calculate the produced pieces and earned minutes for each worker.
   • [Data].[WorkerScanMaster]: Stores worker login information necessary for correlating scan data with worker activity.
   • Essentials].[Worker]: Contains worker details such as WorkerCode, Department, and Designation, which are joined to enrich the scanning information.
   • [Essentials].[Line]: Provides line information linked to the worker scans.
   • dbo.WorkerEfficiencyDataSet: Target table for the calculated worker efficiency data.

2. Temporary Tables:

   • #ScanningInfo: A temporary table that holds aggregated scanning data for each worker, including total pieces produced and earned minutes.
   • #EfficiencyDataSet: A temporary table that stores the detailed efficiency dataset before it is inserted into the permanent table.

These dependencies ensure that the stored procedure can accurately compute and store the worker efficiency metrics.

Parameters

This procedure does not accept any input parameters.

Query Breakdown

---

1. Temporary Table Management:

• Checks if the temporary tables (#ScanningInfo and #EfficiencyDataSet) exist and drops it if they do.

-- Drop temporary tables if they exist
IF OBJECT_ID('tempdb..#ScanningInfo') IS NOT NULL
    DROP TABLE #ScanningInfo;

IF OBJECT_ID('tempdb..#EfficiencyDataSet') IS NOT NULL
    DROP TABLE #EfficiencyDataSet;

2. Scanning Data Aggregation:

• Utilizes a Common Table Expression (CTE) to aggregate data from the TodayPieceWiseScanMasterDN table. This aggregation includes calculating the total number of pieces produced by each worker and the total earned minutes based on their SMV.

WITH ScanningInfo AS (
    SELECT
        ScanID,
        WorkerID,
        WorkerCode,
        WorkerDescription,
        LineID,
        LineCode,
        LineDescription,
        OperationID,
        OperationDescription,
        SMV,
        CreatedAtDate,
        MAX(CreatedAt) AS LastBundleScanTime,
        COUNT(PieceID) AS ProducedPieces,
        (SMV * COUNT(PieceID)) AS EarnedMinutes
    FROM
        [Data].[TodayPieceWiseScanMasterDN]
    WHERE
        WorkerID IS NOT NULL
    GROUP BY
        ScanID,
        WorkerID,
        WorkerCode,
        WorkerDescription,
        LineID,
        LineCode,
        LineDescription,
        OperationID,
        OperationDescription,
        SMV,
        CreatedAtDate
)

3. Worker Login Data Integration:

• Joins the aggregated scanning data with worker login information from the WorkerScanMaster table and retrieves relevant line details. This integration enables the calculation of worker efficiency in the context of their operational environment.

SELECT
     si.*, ISNULL(Dep_Desc,'N/A') AS DepartmentName, ISNULL(Desg_Desc,'N/A') AS Designation
INTO
    #ScanningInfo
FROM
    ScanningInfo si
LEFT JOIN [Essentials].[Worker] w
    ON w.WorkerCode = si.WorkerCode;

-- Common Table Expression to calculate EfficiencyDataSet
WITH EfficiencyDataSet AS (
        SELECT
            si.ScanID,
            si.WorkerID,
            si.WorkerCode,
            si.WorkerDescription,
      si.DepartmentName,
      si.Designation,
            si.LineID,
            si.LineCode,
            si.LineDescription,
            l.CompanyId,
            l.BrId,
            si.OperationID,
            si.OperationDescription,
            si.ProducedPieces,
            si.SMV,
            si.EarnedMinutes,
            si.CreatedAtDate AS ScanningDate,
            CONVERT(DATE, ws.CreatedAt) AS LogInDate,
            si.LastBundleScanTime,
            ws.CreatedAt AS LogInTime
        FROM
            #ScanningInfo si
        JOIN
            [172.16.21.255].[Scanning].[Data].[WorkerScanMaster] ws
            ON si.ScanID = ws.WorkerScanID
            AND si.WorkerID = ws.WorkerID
            AND si.LineID = ws.LineID
            AND si.CreatedAtDate = CONVERT(DATE, ws.CreatedAt)
        JOIN
            [172.16.21.255].[ActiveSooperWizerNCL].[Essentials].[Line] l
            ON l.LineID = si.LineID
    )
    SELECT
        *
    INTO
        #EfficiencyDataSet
    FROM
        EfficiencyDataSet;

4. Final Data Storage:

• After processing, the aggregated efficiency data is inserted into the permanent table WorkerEfficiencyDataSet, making it readily available for reporting and analysis.

-- Insert data into the final table
INSERT INTO  dbo.WorkerEfficiencyDataSet
SELECT
    *
FROM
    #EfficiencyDataSet;

5. Cleanup:

• Finally, the procedure ensures that temporary tables are dropped to free up resources and maintain a clean database environment.

-- Drop temporary tables if they exist
IF OBJECT_ID('tempdb..#ScanningInfo') IS NOT NULL
    DROP TABLE #ScanningInfo;

IF OBJECT_ID('tempdb..#EfficiencyDataSet') IS NOT NULL
    DROP TABLE #EfficiencyDataSet;

Example Usage

To calculate and store worker efficiency data, you simply need to execute the stored procedure:

EXEC [dbo].[uspCalculateWorkerEfficiency];

Summary

The stored procedure [dbo].[uspCalculateWorkerEfficiency] efficiently calculates worker efficiency based on scanning and login data. It uses temporary tables to manage intermediate data, ensuring that the final dataset includes comprehensive details on worker production, line assignment, operation specifics, and earned minutes. Robust error handling is included to ensure data integrity, and temporary tables are dropped after execution to maintain a clean environment.