机器实际产能
公式:(机器实际上班时间 - 停机时间) / 机器实际加工时间 * 标准夹具数
代码
CREATE FUNCTION [dbo].[udf_ActualCapacity]
(
@MachineId SMALLINT,
@OperatorDate DATETIME
)
RETURNS decimal(18,6)
AS
BEGIN
DECLARE @ReturnValue DECIMAL(18,6)
--取得机器实际上班时间
DECLARE @WorkTime DECIMAL(18,6)
SELECT @WorkTime = (SELECT SUM([WorkTime]) FROM [dbo].[udf_WorkTicketData_1]() WHERE [MachineId] = @MachineId AND [OperatorDate] = @OperatorDate)
--取得机器停机时间
DECLARE @MachineHaltTime DECIMAL(18,6)
SELECT @MachineHaltTime = (SELECT SUM([MachineHaltTime]) FROM [dbo].[udf_WorkTicketData_1]() WHERE [MachineId] = @MachineId AND [OperatorDate] = @OperatorDate)
--计算机器运作时间
DECLARE @RealWorkTime DECIMAL(18,6)
SELECT @RealWorkTime = (ISNULL(@WorkTime,0) * 3600 - ISNULL(@MachineHaltTime,0) * 60)
--机器实际加工时间
DECLARE @ProcessTime DECIMAL(18,6)
SELECT @ProcessTime = (SELECT (AVG(ISNULL([ProcessTime],0))) FROM dbo.udf_WorkTicketData_1() WHERE [MachineId] = @MachineId AND [OperatorDate] = @OperatorDate)
--从机器参数表,取得机器的标准夹具数
DECLARE @StandJigNumber TINYINT
SELECT @StandJigNumber = (SELECT [JigNumber] FROM [MachineParameter] WHERE [MachineId] = @MachineId AND (YEAR([MonthName])= YEAR(@OperatorDate)) AND (MONTH([MonthName]) = MONTH(@OperatorDate)))
--计算机器实际产能
SELECT @ReturnValue=(CASE WHEN @ProcessTime = 0 THEN NULL WHEN @ProcessTime IS NULL THEN NULL WHEN @ProcessTime <> 0 THEN ((ISNULL(@RealWorkTime,0) / @ProcessTime) * ISNULL(@StandJigNumber,0)) END)
RETURN @ReturnValue
END
(
@MachineId SMALLINT,
@OperatorDate DATETIME
)
RETURNS decimal(18,6)
AS
BEGIN
DECLARE @ReturnValue DECIMAL(18,6)
--取得机器实际上班时间
DECLARE @WorkTime DECIMAL(18,6)
SELECT @WorkTime = (SELECT SUM([WorkTime]) FROM [dbo].[udf_WorkTicketData_1]() WHERE [MachineId] = @MachineId AND [OperatorDate] = @OperatorDate)
--取得机器停机时间
DECLARE @MachineHaltTime DECIMAL(18,6)
SELECT @MachineHaltTime = (SELECT SUM([MachineHaltTime]) FROM [dbo].[udf_WorkTicketData_1]() WHERE [MachineId] = @MachineId AND [OperatorDate] = @OperatorDate)
--计算机器运作时间
DECLARE @RealWorkTime DECIMAL(18,6)
SELECT @RealWorkTime = (ISNULL(@WorkTime,0) * 3600 - ISNULL(@MachineHaltTime,0) * 60)
--机器实际加工时间
DECLARE @ProcessTime DECIMAL(18,6)
SELECT @ProcessTime = (SELECT (AVG(ISNULL([ProcessTime],0))) FROM dbo.udf_WorkTicketData_1() WHERE [MachineId] = @MachineId AND [OperatorDate] = @OperatorDate)
--从机器参数表,取得机器的标准夹具数
DECLARE @StandJigNumber TINYINT
SELECT @StandJigNumber = (SELECT [JigNumber] FROM [MachineParameter] WHERE [MachineId] = @MachineId AND (YEAR([MonthName])= YEAR(@OperatorDate)) AND (MONTH([MonthName]) = MONTH(@OperatorDate)))
--计算机器实际产能
SELECT @ReturnValue=(CASE WHEN @ProcessTime = 0 THEN NULL WHEN @ProcessTime IS NULL THEN NULL WHEN @ProcessTime <> 0 THEN ((ISNULL(@RealWorkTime,0) / @ProcessTime) * ISNULL(@StandJigNumber,0)) END)
RETURN @ReturnValue
END