DECLARE @ExceptionLocationId CHAR(12)='',
@ExceptionNumber CHAR(12)='',
@AssetName NVARCHAR(100)='',
@WorkcenterId NVARCHAR(100)='',
@ExceptionTypeName NVARCHAR(100)='',
@ProductionExceptionManagementId CHAR (12) --主表主键ID
---创建临时表
--------创建故障维修记录临时表-------------------------------
IF OBJECT_ID('tempdb..#TB') IS NOT NULL
DROP TABLE #TB
CREATE TABLE #TB(
字段1 NVARCHAR(100), ---场地
字段2 NVARCHAR(200), --生产线
字段3 NVARCHAR(200), --设备名称
)
----定义游标---
DECLARE PemID_cursor CURSOR LOCAL
FOR
SELECT ProductionExceptionManagementId FROM dbo.ProductionExceptionManagement WHERE ExceptionStatusId<>'' ---状态不能为空
FOR READ ONLY
---打开游标
OPEN PemID_cursor
---读取数据
FETCH NEXT FROM PemID_cursor
INTO @ProductionExceptionManagementId
WHILE @@FETCH_STATUS=0
BEGIN
SELECT
@ExceptionLocationId = ExceptionLocationId
FROM ExceptionLocation Ex LEFT OUTER JOIN dbo.ProductionExceptionManagement Pd ON Ex.FactoryId=pd.FactoryId AND Ex.SiteId=Pd.SiteId
AND EX.WorkcenterId=Pd.WorkcenterId WHERE Ex.ExceptionTypeId=Pd.ExceptionTypeId AND ProductionExceptionManagementId=@ProductionExceptionManagementId
-----查找单号-----------------
SET @ExceptionNumber=(SELECT ExceptionNumber FROM dbo.ProductionExceptionManagement WHERE ProductionExceptionManagementid=@ProductionExceptionManagementId)
---主从表中需要查找匹配的字段值1，字段值2，字段值3......---
SET @字段1=()
SET @字段2=()
...........
--- XCWaitTime AS 现场恢复等待时长
--SET @XCWaitTime=(SELECT DATEDIFF(MINUTE,@FailureTime,@XCHandleTime))
SET @XCWaitTime=CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 60 AS INT ) AS VARCHAR ) + '秒'
---现场恢复等待时长----
---MaintenanceDuration AS 修护课维修时长
---SET @MaintenanceDuration=(SELECT DATEDIFF(MINUTE,@HandleTime,@XCHandleTime))
SET @MaintenanceDuration=CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 60 AS INT ) AS VARCHAR ) + '秒'
---修护课维修时长
---主从表中需要查找匹配的字段值1，字段值2，字段值3......
--------临时表插入值-------------------------------
INSERT INTO #TB (
字段1 ,
字段2 ,
字段3,
.....
)
SELECT
@字段1 ,
@字段2 ,
@字段3
.....
--进入下一行数据--
FETCH NEXT FROM PemID_cursor
INTO @ProductionExceptionManagementId
END
---关闭游标--------------
CLOSE PemID_cursor
---释放游标------------------
DEALLOCATE PemID_cursor
----统计展示重复数据次数前五----
SELECT TOP 5 字段1,COUNT(0) AS 次数 FROM #TB WHERE 字段2='自动化D/C1线' GROUP BY 字段1 HAVING COUNT(字段1)>1 ORDER BY 次数 DESC
----设备停机时长----
SELECT TOP 5 字段1 AS 名称1, 字段3 AS 名称3 FROM #TB WHERE 字段2='自动化D/C4线' ORDER BY 字段2 desc
----删除临时表---
DROP TABLE #TB