--EXEC . 6,2,40
CREATE PROCEDURE .--取得报废
@plantID VARCHAR(3)='6',--工厂ID
@Type INT=1,--1表示按责任部门；2表示安装报废原因
@TopNumber INT=20--前名次
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
IF @Type=1
SET @SQL='
IF OBJECT_ID(''tempdb..#Tmp'') IS NOT NULL
DROP TABLE #Tmp
SELECT IDENTITY(INT,1,1) AS ID,责任部门,SUM(报废面积) AS 报废面积 ,CAST(0 AS NUMERIC(18,2)) AS 比率
INTO #Tmp
FROM ERPScrapt
WHERE RKEY15='+CAST(@plantID AS VARCHAR(2))+'
GROUP BY 责任部门,RKEY15
ORDER BY SUM(报废面积) DESC
UPDATE A SET A.比率=B.比率/dbo.GetSumSrapt('+CAST(@plantID AS VARCHAR(2))+')
FROM #Tmp A, (SELECT B1.ID,ISNULL(SUM(B2.报废面积),0) AS 比率
FROM #Tmp B1,#Tmp B2
WHERE B1.ID>=B2.ID
GROUP BY B1.ID) B
WHERE A.ID=B.ID
SELECT TOP '+CAST(@TopNumber AS VARCHAR(3))+' 责任部门,报废面积, 比率 FROM #Tmp
ORDER BY ID'
IF @Type=2
SET @SQL='
IF OBJECT_ID(''tempdb..#Tmp'') IS NOT NULL
DROP TABLE #Tmp
SELECT IDENTITY(INT,1,1) AS ID,报废原因,SUM(报废面积) AS 报废面积 ,CAST(0 AS NUMERIC(18,2)) AS 比率
INTO #Tmp
FROM ERPScrapt
WHERE RKEY15='+CAST(@plantID AS VARCHAR(2))+'
GROUP BY 报废原因,RKEY15
ORDER BY SUM(报废面积) DESC
UPDATE A SET A.比率=B.比率/dbo.GetSumSrapt('+CAST(@plantID AS VARCHAR(2))+')
FROM #Tmp A, (SELECT B1.ID,ISNULL(SUM(B2.报废面积),0) AS 比率
FROM #Tmp B1,#Tmp B2
WHERE B1.ID>=B2.ID
GROUP BY B1.ID) B
WHERE A.ID=B.ID
SELECT TOP '+CAST(@TopNumber AS VARCHAR(3))+' 报废原因,报废面积, 比率 FROM #Tmp
ORDER BY ID
'
EXEC(@SQL)
END
D:\diaoyong.jpg
