-----------供应链管理部KPI得分--------

-----------供应链管理部KPI得分--------
SELECT AA.*,BB.warning,BB.target,BB.challenge,BB.weight,BB.coefficient,
CASE WHEN AA.ratio*BB.coefficient<BB.warning*BB.coefficient THEN 0
WHEN AA.ratio*BB.coefficient<=BB.target*BB.coefficient THEN ROUND(60+(AA.ratio-BB.warning)/(BB.target-BB.warning)*20,2)
WHEN AA.ratio*BB.coefficient<=BB.challenge*BB.coefficient THEN ROUND(80+(AA.ratio-BB.target)/(BB.challenge-BB.target)*20,2)
ELSE 100 END DF
FROM (
SELECT *
FROM dbo.KPI_BI_HISTORY a
WHERE SUBSTRING(a.syear,1,4) =YEAR(GETDATE()-1))AA
LEFT JOIN dbo.KPI_BI_TARGET BB
ON BB.department = AA.department
AND BB.index_name = AA.index_name
AND BB.smonth = AA.smonth
AND BB.syear = AA.syear
AND BB.ttype = AA.TTYPE