公式:(機器實際上班時間 - 停機時間) / 機器标準時間 * 标準夾具數
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISPrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdsATOfd3bkFGazxCMx8VesATMfhHLlN3XnxCMwEzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-YWan5SZlJWY2YjZzgTZyMTYwUTMkNTZ4UTM5MzYxY2Y1QTO58CXyAzLchDMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjL4M3Lc9CX6MHc0RHaiojIsJye.gif)
代碼
CREATE FUNCTION [dbo].[udf_TheoreticalDutyTwo]
(
@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 @StandTime DECIMAL(18,6)
DECLARE @StandJigNumber TINYINT
SELECT @StandTime = [StandardTime],@StandJigNumber = [JigNumber] FROM [MachineParameter] WHERE [MachineId] = @MachineId AND (YEAR([MonthName])= YEAR(@OperatorDate)) AND (MONTH([MonthName]) = MONTH(@OperatorDate))
--計算機器的理論能力2
SELECT @ReturnValue=(CASE WHEN @StandTime = 0 THEN NULL WHEN @StandTime IS NULL THEN NULL WHEN @StandTime <> 0 THEN ((ISNULL(@RealWorkTime,0) / @StandTime) * ISNULL(@StandJigNumber,0)) END)
RETURN @ReturnValue
END