DECLARE @EndDate DATE = GETDATE();
DECLARE @StartDate DATE = DATEADD(DAY, -365, @EndDate);
-- 创建部门临时表
IF OBJECT_ID('tempdb..#TEMP_Department') IS NOT NULL DROP TABLE #TEMP_Department;
CREATE TABLE #TEMP_Department (Department nvarchar(20));
INSERT INTO #TEMP_Department EXEC [ZY_P_Get_SAP_Account_to_TempDept];
-- 基础销售额计算（只按客户编号分组）
IF OBJECT_ID('tempdb..#Sales') IS NOT NULL DROP TABLE #Sales;
SELECT
T.客户编号,
MAX(T.客户名称) AS 客户名称, -- 取最大名称作为代表
SUM(T.RMB总价) AS 销售额
INTO #Sales
FROM [ZY_TB_SALES_Details] T
INNER JOIN #TEMP_Department D ON T.综合部门 = D.Department
WHERE T.交货日期 BETWEEN @StartDate AND @EndDate
AND T.业务体系 = '经销体系'
AND (T.客户编号 COLLATE Latin1_General_CI_AS LIKE 'c%')
GROUP BY T.客户编号;
-- 1. 计算总毛利（非项目+项目）
IF OBJECT_ID('tempdb..#NonProjectProfit') IS NOT NULL DROP TABLE #NonProjectProfit;
SELECT
T.客户编号,
MAX(T.客户名称) AS 客户名称, -- 取最大名称作为代表
SUM(T.扣技服后毛利) AS 非项目毛利
INTO #NonProjectProfit
FROM [ZY_TB_SALES_Details] T
INNER JOIN #TEMP_Department D ON T.综合部门 = D.Department
WHERE T.交货日期 BETWEEN @StartDate AND @EndDate
AND T.业务体系 = '经销体系'
AND T.项目合同YN IS NULL
AND (T.客户编号 COLLATE Latin1_General_CI_AS LIKE 'c%')
GROUP BY T.客户编号;
IF OBJECT_ID('tempdb..#ProjectProfit') IS NOT NULL DROP TABLE #ProjectProfit;
SELECT
客户编号,
MAX(客户名称) AS 客户名称, -- 取最大名称作为代表
SUM(有效综合毛利RMB) AS 项目毛利
INTO #ProjectProfit
FROM ZY_TB_JX_XM_SalesPer_Profit
WHERE 交货日期 BETWEEN @StartDate AND @EndDate
AND 综合部门 IN (SELECT Department FROM #TEMP_Department)
AND (客户编号 COLLATE Latin1_General_CI_AS LIKE 'c%')
GROUP BY 客户编号;
IF OBJECT_ID('tempdb..#ProfitScore') IS NOT NULL DROP TABLE #ProfitScore;
SELECT
COALESCE(N.客户编号, P.客户编号) AS 客户编号,
COALESCE(N.客户名称, P.客户名称) AS 客户名称, -- 合并后名称
ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) AS 总毛利,
CASE
WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 1000000 THEN 100
WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 500000 THEN 80
WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 100000 THEN 60
WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) >= 10000 THEN 40
WHEN ISNULL(N.非项目毛利, 0) + ISNULL(P.项目毛利, 0) > 0 THEN 20
ELSE 0
END * 0.3 AS 毛利得分
INTO #ProfitScore
FROM #NonProjectProfit N
FULL OUTER JOIN #ProjectProfit P ON N.客户编号 = P.客户编号;
-- 2. 计算预收款得分（只按客户编号分组）
IF OBJECT_ID('tempdb..#Advance') IS NOT NULL DROP TABLE #Advance;
WITH CTE_OACT AS (SELECT AcctCode, AcctName FROM OACT),
CTE_BANK AS (
SELECT DISTINCT Number AS '进账单流水号', BankName AS '银行信息' FROM [WEB_BANKINCOME]
UNION ALL
SELECT 到帐单流水号 AS '进账单流水号', 银行信息 FROM [ZY_TB_WEB_BANKINCOME_OLD]
),
CTE_Result AS (
SELECT
T0.Cardcode AS N'客户编号',
T0.CardName AS N'客户名称',
T0.OpenBal AS N'未清金额',
T0.Canceled
FROM ORCT T0
INNER JOIN CTE_OACT t3 ON t0.TrsfrAcct = t3.AcctCode
LEFT JOIN (SELECT DISTINCT 进账单流水号, 银行信息 FROM CTE_BANK) M0
ON M0.进账单流水号 = T0.U_LSH
LEFT JOIN [ZY_VIEW_XM] X0 ON X0.ConCode = T0.U_contractnumber
LEFT JOIN OCRD d1 ON d1.CardCode = t0.CardCode
WHERE T0.taxdate<= @EndDate
AND T0.U_Department IN ('上海办','生命科学部','分销部','工业部','天津办','基础科研部','沈阳办','烟台办','广州办','成都办','商务部')
AND (T0.Cardcode COLLATE Latin1_General_CI_AS LIKE 'c%')
)
SELECT
[客户编号],
MAX([客户名称]) AS 客户名称, -- 取最大名称作为代表
SUM(CASE WHEN Canceled = 'N' THEN [未清金额] ELSE 0 END) AS 预收款金额
INTO #Advance
FROM CTE_Result
GROUP BY [客户编号];
IF OBJECT_ID('tempdb..#AdvanceScore') IS NOT NULL DROP TABLE #AdvanceScore;
SELECT
A.客户编号,
A.客户名称,
A.预收款金额,
(ISNULL(占比得分, 0) + ISNULL(金额得分, 0)) / 2 * 0.2 AS 预收款得分
INTO #AdvanceScore
FROM #Advance A
LEFT JOIN #Sales S ON A.客户编号 = S.客户编号
CROSS APPLY (
SELECT
CASE
WHEN 预收款金额 / NULLIF(S.销售额, 0) >= 1 THEN 100
WHEN 预收款金额 / NULLIF(S.销售额, 0) >= 0.5 THEN 75
WHEN 预收款金额 / NULLIF(S.销售额, 0) >= 0.1 THEN 50
WHEN 预收款金额 > 0 THEN 25
ELSE 0
END AS 占比得分,
CASE
WHEN 预收款金额 >= 1000000 THEN 100
WHEN 预收款金额 >= 500000 THEN 75
WHEN 预收款金额 >= 100000 THEN 50
WHEN 预收款金额 > 0 THEN 25
ELSE 0
END AS 金额得分
) AS Scores;
-- 3. 计算超期应收款得分（只按客户编号分组）
IF OBJECT_ID('tempdb..#OverdueBase') IS NOT NULL DROP TABLE #OverdueBase;
SELECT
T.客户编码 AS 客户编号,
T.客户名称,
T.未收款金额,
ISNULL(X.XM_YN, 'N') AS 项目合同,
T.DN日期 AS 销货日期,
T.业务体系
INTO #OverdueBase
FROM [ZY_TB_Receivable_Balance_Base] T
INNER JOIN #TEMP_Department X0 ON X0.Department = T.综合部门
LEFT JOIN [ZY_VIEW_XM] X ON X.ConCode = T.合同号
WHERE T.业务体系 = '经销体系'
AND (T.客户编码 COLLATE Latin1_General_CI_AS LIKE 'c%');
IF OBJECT_ID('tempdb..#Overdue') IS NOT NULL DROP TABLE #Overdue;
SELECT
客户编号,
MAX(客户名称) AS 客户名称, -- 取最大名称作为代表
SUM(未收款金额) AS 超期应收款
INTO #Overdue
FROM #OverdueBase
WHERE
项目合同 = 'N'
AND DATEDIFF(DAY, 销货日期, @EndDate) >= 180
AND (客户编号 COLLATE Latin1_General_CI_AS LIKE 'c%')
GROUP BY 客户编号;
IF OBJECT_ID('tempdb..#OverdueScore') IS NOT NULL DROP TABLE #OverdueScore;
SELECT
O.客户编号,
O.客户名称,
O.超期应收款,
(ISNULL(占比得分, 100) + ISNULL(金额得分, 100)) / 2 * 0.3 AS 超期得分
INTO #OverdueScore
FROM #Overdue O
LEFT JOIN #Sales S ON O.客户编号 = S.客户编号
CROSS APPLY (
SELECT
CASE
WHEN 超期应收款 / NULLIF(S.销售额, 0) >= 1 THEN 0
WHEN 超期应收款 / NULLIF(S.销售额, 0) >= 0.5 THEN 25
WHEN 超期应收款 / NULLIF(S.销售额, 0) >= 0.1 THEN 50
WHEN 超期应收款 > 0 THEN 75
ELSE 100
END AS 占比得分,
CASE
WHEN 超期应收款 >= 1000000 THEN 0
WHEN 超期应收款 >= 500000 THEN 25
WHEN 超期应收款 >= 100000 THEN 50
WHEN 超期应收款 > 0 THEN 75
ELSE 100
END AS 金额得分
) AS Scores;
-- 4. 计算库存得分（只按客户编号分组）
IF OBJECT_ID('tempdb..#Inventory') IS NOT NULL DROP TABLE #Inventory;
SELECT
[客户编号],
MAX([客户名称]) AS 客户名称, -- 取最大名称作为代表
SUM([含税总价RMB]) AS 库存金额
INTO #Inventory
FROM (
SELECT
R0.Cardcode AS '客户编号',
R0.CardName AS '客户名称',
CASE
WHEN A0.U_CustomsRate IS NOT NULL THEN
CASE
WHEN A0.U_Import ='Y' AND A0.U_RevisedDuty IS NOT NULL
THEN (A0.U_ForeignPrice * A0.U_CustomsRate + ISNULL(A0.U_RevisedDuty,0)) * (A0.U_InputVATRate/100+1)
WHEN A0.U_Import ='Y' AND A0.U_RevisedDuty IS NULL
THEN (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0)) * (A0.U_InputVATRate/100+1)
ELSE (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0))
END * B0.Quantity
ELSE
CASE
WHEN A0.U_Import ='Y'
THEN (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0)) * (A0.U_InputVATRate/100+1)
ELSE (A0.U_PriceAfVAT + ISNULL(A0.U_Tariff,0))
END * B0.Quantity
END AS '含税总价RMB'
FROM OBTN A0
JOIN OIBT B0 ON B0.ItemCode = A0.ItemCode
AND B0.BatchNum = A0.DistNumber
AND B0.SysNumber = A0.SysNumber
LEFT JOIN ORDR R0 ON R0.SupplCode = A0.U_SaleContNo
WHERE
B0.Quantity > 0
AND DATEDIFF(DAY, A0.InDate, GETDATE()) >= 60
AND R0.CardName IS NOT NULL
AND A0.U_Department IN ('上海办','生命科学部','分销部','工业部','天津办','基础科研部','沈阳办','烟台办','广州办','成都办','商务部')
AND (R0.Cardcode COLLATE Latin1_General_CI_AS LIKE 'c%')
) AS SubQuery
GROUP BY [客户编号];
IF OBJECT_ID('tempdb..#InventoryScore') IS NOT NULL DROP TABLE #InventoryScore;
SELECT
I.客户编号,
I.客户名称,
I.库存金额,
CASE
WHEN ISNULL(占比得分, 100) < ISNULL(金额得分, 100)
THEN ISNULL(占比得分, 100)
ELSE ISNULL(金额得分, 100)
END * 0.2 AS 库存得分
INTO #InventoryScore
FROM #Inventory I
LEFT JOIN #Sales S ON I.客户编号 = S.客户编号
CROSS APPLY (
SELECT
CASE
WHEN 库存金额 / NULLIF(S.销售额, 0) >= 1 THEN 0
WHEN 库存金额 / NULLIF(S.销售额, 0) >= 0.5 THEN 25
WHEN 库存金额 / NULLIF(S.销售额, 0) >= 0.1 THEN 50
WHEN 库存金额 > 0 THEN 75
ELSE 100
END AS 占比得分,
CASE
WHEN 库存金额 >= 1000000 THEN 0
WHEN 库存金额 >= 500000 THEN 25
WHEN 库存金额 >= 100000 THEN 50
WHEN 库存金额 > 0 THEN 75
ELSE 100
END AS 金额得分
) AS Scores;
-- 5. 汇总最终结果（按客户编号分组，使用FOR XML PATH合并名称）
;WITH CombinedData AS (
SELECT
COALESCE(P.客户编号, A.客户编号, O.客户编号, I.客户编号) AS 客户编号,
COALESCE(P.客户名称, A.客户名称, O.客户名称, I.客户名称) AS 客户名称,
P.总毛利,
P.毛利得分,
A.预收款金额,
A.预收款得分,
O.超期应收款,
O.超期得分,
I.库存金额,
I.库存得分
FROM #ProfitScore P
FULL OUTER JOIN #AdvanceScore A ON P.客户编号 = A.客户编号
FULL OUTER JOIN #OverdueScore O ON P.客户编号 = O.客户编号
FULL OUTER JOIN #InventoryScore I ON P.客户编号 = I.客户编号
),
DistinctNames AS (
SELECT
客户编号,
STUFF((
SELECT DISTINCT ', ' + tmp.客户名称
FROM CombinedData tmp
WHERE tmp.客户编号 = CombinedData.客户编号
AND tmp.客户名称 IS NOT NULL
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
) AS 客户名称
FROM CombinedData
GROUP BY 客户编号
)
SELECT
C.客户编号,
DN.客户名称,
SUM(ISNULL(C.总毛利, 0)) AS 总毛利,
SUM(ISNULL(C.毛利得分, 0)) AS 毛利得分,
SUM(ISNULL(C.预收款金额, 0)) AS 预收款金额,
SUM(ISNULL(C.预收款得分, 0)) AS 预收款得分,
SUM(ISNULL(C.超期应收款, 0)) AS 超期应收款,
SUM(ISNULL(C.超期得分, 0)) AS 超期得分,
SUM(ISNULL(C.库存金额, 0)) AS 库存金额,
SUM(ISNULL(C.库存得分, 0)) AS 库存得分,
ROUND(
SUM(ISNULL(C.毛利得分, 0)) +
SUM(ISNULL(C.预收款得分, 0)) +
SUM(ISNULL(C.超期得分, 0)) +
SUM(ISNULL(C.库存得分, 0)),
2) AS 总得分
FROM CombinedData C
INNER JOIN DistinctNames DN ON C.客户编号 = DN.客户编号
GROUP BY C.客户编号, DN.客户名称
ORDER BY 总得分 DESC;
-- 清理所有临时表
DROP TABLE #Sales, #NonProjectProfit, #ProjectProfit, #ProfitScore,
#Advance, #AdvanceScore, #TEMP_Department, #OverdueBase,
#Overdue, #OverdueScore, #Inventory, #InventoryScore;
在这个代码的基础上修改，得分不变，最终展示应收款不需要加“销货日期”≥180天的条件，库存金额不需要加入库天数”≥60天的条件，但是计算得分的时候还是需要
本文详细介绍了collateLatin1_General_CI_AS这一排序规则的具体含义及其应用场景，对于理解不同数据库环境下字符排序及比较的行为有重要作用。
扫一扫
到【灌水乐园】发言
1万+
