if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_getRptP1_plus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_getRptP1_plus]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [dbo].[usp_getRptP1_plus] 
	@EduYear smallint,
	@UnivID varchar(5),
	@FacID varchar(5),
	@MissionID varchar

AS
SELECT  TB1.KpiIndex, TB1.KpiCalTypeID, CASE WHEN IsNull(CarIsPass, 0) = 0 THEN 'x' WHEN CarIsPass = 1 THEN '/' END AS IsPass,TB2.KpiTarget, TB2.AfterKpiValues, ISNULL(TB2.CarScore, 0) AS CarScore, ISNULL(TB2.AfterFormulaBase, 0) AS AfterFormulaBase, ISNULL(TB2.AfterFormulaDiv, 0) AS AfterFormulaDiv,CarComment
FROM         (SELECT     dbo.CHEQA_KPI.KpiID, dbo.CHEQA_KPI.KpiIndex, dbo.CHEQA_KPI.KpiCalTypeID
                       FROM          dbo.CHEQA_KPI INNER JOIN
                                              dbo.CHEQA_KpiSetting ON dbo.CHEQA_KPI.KpiID = dbo.CHEQA_KpiSetting.KpiID
                       WHERE      (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND (dbo.CHEQA_KpiSetting.UnivID = @UnivID) AND 
                                              (dbo.CHEQA_KpiSetting.FacID = @FacID)) AS TB1 LEFT OUTER JOIN
                          (SELECT     KpiID, KpiTarget, CarIsPass, AfterKpiValues, CarScore, AfterFormulaBase, AfterFormulaDiv,CarComment
                            FROM          dbo.CHEQA_SAR
                            WHERE      (EduYear = @EduYear) AND (UnivID = @UnivID) AND (FacID = @FacID)) AS TB2 ON TB1.KpiID = TB2.KpiID
ORDER BY CAST(LEFT(TB1.KpiIndex, CHARINDEX('.', TB1.KpiIndex) - 1) AS int), CAST(RIGHT(TB1.KpiIndex, LEN(TB1.KpiIndex) - CHARINDEX('.', TB1.KpiIndex)) AS decimal(9, 2))

------------------------
SELECT     ISNULL(AVG(CAST(ISNULL(CarScore, 0) AS Decimal(18, 3))), 0) AS AvgAll
FROM         (SELECT     dbo.CHEQA_KpiSetting.KpiID, dbo.CHEQA_KPI.KpiTypeID, dbo.CHEQA_KpiSetting.ObjID, CHEQA_SAR_1.CarScore
                       FROM          dbo.CHEQA_KpiSetting INNER JOIN
                                              dbo.CHEQA_KPI ON dbo.CHEQA_KpiSetting.KpiID = dbo.CHEQA_KPI.KpiID LEFT OUTER JOIN
                                                  (SELECT     KpiID, CarScore
                                                    FROM          dbo.CHEQA_SAR
                                                    WHERE      (EduYear = @EduYear) AND (FacID = @FacID) AND (UnivID = @UnivID)) AS CHEQA_SAR_1 ON 
                                              dbo.CHEQA_KpiSetting.KpiID = CHEQA_SAR_1.KpiID
                       WHERE      (dbo.CHEQA_KpiSetting.EduYear = @EduYear) AND (dbo.CHEQA_KpiSetting.UnivID = @UnivID) AND 
                                              (dbo.CHEQA_KpiSetting.FacID = @FacID) AND (dbo.CHEQA_KpiSetting.KpiID IN
                                                  (SELECT     KpiID
                                                    FROM          dbo.CHEQA_KpiSetting AS CHEQA_KpiSetting_1
                                                    WHERE    (UnivID = 00000 OR UnivID = @UnivID) AND (EduYear = @EduYear)))) AS Tb


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO