SELECT
CASE
WHEN ISNULL(S.FieldCode, '') = '' THEN 'ZZ'
WHEN ISNULL(S.FieldCode, '') = '!' THEN 'AA'
ELSE S.FieldCode
END AS FieldCode,
CASE
WHEN ISNULL(S.FieldCode, '') = '' THEN '전체'
WHEN ISNULL(S.FieldCode, '') = '!' THEN '미정'
ELSE MAX(dbo.DSCUF_EE_GetCodeName('Field', S.FieldCode, Default, Default))
END AS FieldName,
CASE
WHEN ISNULL(S.FieldCode, '') = '' THEN '총계'
WHEN ISNULL(S.VendorCode, '') = '' THEN '계'
ELSE S.VendorCode
END AS VendorCode,
CASE
WHEN ISNULL(S.FieldCode, '') = '' THEN '총계'
WHEN ISNULL(S.VendorCode, '') = '' THEN '계'
ELSE MAX(S.VendorName)
END AS VendorName,
SUM(S.ContractCnt ) AS ContractCnt,
AVG(S.AvgPoint ) AS AvgPoint,
CASE
WHEN S.VendorCode IS NULL THEN '9999'
ELSE CONVERT(VARCHAR , MAX(Ranking))
END AS Ranking,
MAX(S.Remark) AS Remark
FROM
(
SELECT
ISNULL(C.FieldCode, '!') AS FieldCode,
MAX(dbo.DSCUF_EE_GetCodeName('Field', C.FieldCode, '', '')) AS FieldName,
B.VendorCode AS VendorCode,
MAX(B.CtrtVendorNm) AS VendorName,
COUNT(1) AS ContractCnt,
AVG(A.ApprTotalPoint) AS AvgPoint,
RANK() OVER (
PARTITION BY C.FieldCode
ORDER BY AVG(A.ApprTotalPoint) DESC
) AS Ranking,
'' AS Remark
FROM
dbo.DSCTB_EEApprTargetContract AS A
INNER JOIN dbo.DSCVW_EEContractInfo AS B
ON
B.ContractNum = A.ContractNum
AND B.OrdNum = A.OrdNum
AND 0 =
(
SELECT COUNT(1)
FROM dbo.DSCTB_EEApprExceptContract
WHERE
ContractNum = B.ContractNum
AND OrdNum = B.OrdNum
)
LEFT JOIN dbo.DSCTB_EEContTypeFieldInfo AS C
ON
B.WorkerClsCode = C.ContTypeCode
WHERE
A.ApprFinishYN = 'Y'
AND A.ApprReplyYN = 'N'
GROUP
BY C.FieldCode, B.VendorCode
) AS S
GROUP
BY S.FieldCode, S.VendorCode WITH ROLLUP
