DECLARE @StartDate DATE = DATEADD(DAY, -365, GETDATE());
DECLARE @EndDate DATE = GETDATE();
-- 创建表变量存储部门数据
DECLARE @TEMP_Department TABLE (Department nvarchar(20));
INSERT INTO @TEMP_Department EXEC [ZY_P_Get_SAP_Account_to_TempDept];
-- 基础销售额计算
SELECT
T.客户名称,
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.业务体系 = '经销体系'
GROUP BY T.客户名称;
-- 计算总毛利（非项目+项目）
-- 非项目毛利
SELECT
T.客户名称,
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
GROUP BY T.客户名称;
-- 项目毛利
SELECT
客户名称,
SUM(有效综合毛利RMB) AS 项目毛利
INTO #ProjectProfit
FROM ZY_TB_JX_XM_SalesPer_Profit
WHERE 交货日期 BETWEEN @StartDate AND @EndDate
AND 综合部门 IN (SELECT Department FROM @TEMP_Department)
GROUP BY 客户名称;
-- 计算毛利得分
SELECT
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.客户名称;
-- 计算预收款
SELECT
T0.CardName AS 客户名称,
SUM(CASE WHEN T0.Canceled = 'N' THEN T0.OpenBal ELSE 0 END) AS 预收款金额
INTO #Advance
FROM ORCT T0
INNER JOIN (SELECT AcctCode FROM OACT) t3 ON t0.TrsfrAcct = t3.AcctCode
WHERE T0.taxdate <= @EndDate
AND T0.U_Department IN (SELECT Department FROM @TEMP_Department)
GROUP BY T0.CardName;
-- 计算应收款和超期应收款
SELECT
T.客户名称,
T.未收款金额,
ISNULL(X.XM_YN, 'N') AS 项目合同,
T.DN日期 AS 销货日期
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.业务体系 = '经销体系';
-- 完整应收款
SELECT
客户名称,
SUM(未收款金额) AS 应收款总额
INTO #TotalReceivable
FROM #OverdueBase
WHERE 项目合同 = 'N'
GROUP BY 客户名称;
-- 超期应收款
SELECT
客户名称,
SUM(未收款金额) AS 超期应收款
INTO #Overdue
FROM #OverdueBase
WHERE 项目合同 = 'N'
AND DATEDIFF(DAY, 销货日期, @EndDate) >= 180
GROUP BY 客户名称;
-- 计算库存
SELECT
R0.CardName AS 客户名称,
SUM(
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 库存总额
INTO #TotalInventory
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 R0.CardName IS NOT NULL
GROUP BY R0.CardName;
-- 获取客户主数据
SELECT
CardCode AS 客户编号,
CardName AS 客户名称,
CASE WHEN UPPER(ISNULL(cntctprsn, '')) = 'VIP' THEN 'VIP' ELSE '' END AS 客户类型
INTO #CustomerMaster
FROM OCRD
WHERE Cardtype = 'C';
-- 创建主客户表
SELECT
CM.客户编号,
CM.客户类型,
CM.客户名称,
ISNULL(P.总毛利, 0) AS 近期毛利,
ISNULL(P.毛利得分, 0) AS 毛利得分,
ISNULL(A.预收款金额, 0) AS 预收款金额,
ISNULL(R.应收款总额, 0) AS 应收款总额,
ISNULL(TI.库存总额, 0) AS 库存总额,
ISNULL(O.超期应收款, 0) AS 超期应收款
INTO #MasterClient
FROM #CustomerMaster CM
LEFT JOIN #ProfitScore P ON CM.客户名称 = P.客户名称
LEFT JOIN #Advance A ON CM.客户名称 = A.客户名称
LEFT JOIN #TotalReceivable R ON CM.客户名称 = R.客户名称
LEFT JOIN #TotalInventory TI ON CM.客户名称 = TI.客户名称
LEFT JOIN #Overdue O ON CM.客户名称 = O.客户名称;
-- 计算得分
SELECT
MC.客户名称,
-- 预收款得分
(
CASE
WHEN MC.预收款金额 / NULLIF(S.销售额, 0) >= 1 THEN 100
WHEN MC.预收款金额 / NULLIF(S.销售额, 0) >= 0.5 THEN 75
WHEN MC.预收款金额 / NULLIF(S.销售额, 0) >= 0.1 THEN 50
WHEN MC.预收款金额 > 0 THEN 25
ELSE 0
END +
CASE
WHEN MC.预收款金额 >= 1000000 THEN 100
WHEN MC.预收款金额 >= 500000 THEN 75
WHEN MC.预收款金额 >= 100000 THEN 50
WHEN MC.预收款金额 > 0 THEN 25
ELSE 0
END
) / 2 * 0.2 AS 预收款得分,
-- 超期应收款得分
(
CASE
WHEN MC.超期应收款 / NULLIF(S.销售额, 0) >= 1 THEN 0
WHEN MC.超期应收款 / NULLIF(S.销售额, 0) >= 0.5 THEN 25
WHEN MC.超期应收款 / NULLIF(S.销售额, 0) >= 0.1 THEN 50
WHEN MC.超期应收款 > 0 THEN 75
ELSE 100
END +
CASE
WHEN MC.超期应收款 >= 1000000 THEN 0
WHEN MC.超期应收款 >= 500000 THEN 25
WHEN MC.超期应收款 >= 100000 THEN 50
WHEN MC.超期应收款 > 0 THEN 75
ELSE 100
END
) / 2 * 0.3 AS 超期得分,
-- 库存得分
CASE
WHEN (CASE
WHEN MC.库存总额 / NULLIF(S.销售额, 0) >= 1 THEN 0
WHEN MC.库存总额 / NULLIF(S.销售额, 0) >= 0.5 THEN 25
WHEN MC.库存总额 / NULLIF(S.销售额, 0) >= 0.1 THEN 50
WHEN MC.库存总额 > 0 THEN 75
ELSE 100
END)
<= (CASE
WHEN MC.库存总额 >= 1000000 THEN 0
WHEN MC.库存总额 >= 500000 THEN 25
WHEN MC.库存总额 >= 100000 THEN 50
WHEN MC.库存总额 > 0 THEN 75
ELSE 100
END)
THEN (CASE
WHEN MC.库存总额 / NULLIF(S.销售额, 0) >= 1 THEN 0
WHEN MC.库存总额 / NULLIF(S.销售额, 0) >= 0.5 THEN 25
WHEN MC.库存总额 / NULLIF(S.销售额, 0) >= 0.1 THEN 50
WHEN MC.库存总额 > 0 THEN 75
ELSE 100
END)
ELSE (CASE
WHEN MC.库存总额 >= 1000000 THEN 0
WHEN MC.库存总额 >= 500000 THEN 25
WHEN MC.库存总额 >= 100000 THEN 50
WHEN MC.库存总额 > 0 THEN 75
ELSE 100
END)
END * 0.2 AS 库存得分
INTO #Scores
FROM #MasterClient MC
LEFT JOIN #Sales S ON MC.客户名称 = S.客户名称;
-- 汇总最终结果
SELECT
MC.客户编号,
MC.客户类型,
MC.客户名称,
ROUND(
ISNULL(MC.毛利得分, 0) +
ISNULL(S.预收款得分, 0) +
ISNULL(S.超期得分, 0) +
ISNULL(S.库存得分, 0),
2) AS 用户评级,
MC.应收款总额 AS 应收款,
MC.预收款金额 AS 预收款,
MC.库存总额 AS 专项库存,
MC.近期毛利 AS 近期毛利,
'' AS 备注2
FROM #MasterClient MC
LEFT JOIN #Scores S ON MC.客户名称 = S.客户名称
ORDER BY 用户评级 DESC;
-- 清理所有临时表
DROP TABLE #Sales, #NonProjectProfit, #ProjectProfit, #ProfitScore,
#Advance, #OverdueBase, #Overdue, #CustomerMaster,
#MasterClient, #TotalReceivable, #TotalInventory, #Scores; 最终结果再加上 主键id 列 格式为 年月日时分秒+3为数字 例如： 202507311356001 这样的15为数字 然后把这个代码写成储存过程
本文探讨了完美的云图形成及其重要性，并预测了在N34，E112附近将出现显著的变化，比喻为五上的变形金刚现象。
扫一扫
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
