CREATE PROCEDURE ZY_P_callcenter_data_push
AS
BEGIN
SET NOCOUNT ON;
-- 日期范围参数
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 '703192237850693' ELSE '703192237846597' 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.客户名称;
-- 创建客户信息临时表
CREATE TABLE #CustomerInfo (
客户名称 NVARCHAR(100),
合同总数 INT,
金额总数 DECIMAL(18,2),
最近下单日期 DATETIME,
下单金额最多的商品top3 NVARCHAR(MAX),
销售员 NVARCHAR(MAX),
联系人姓名 NVARCHAR(100),
联系人电话 NVARCHAR(100),
联系人来源 NVARCHAR(20)
);
-- 填充客户信息数据
WITH BaseData AS (
SELECT
o."CardName",
o."DocEntry",
o."DocDate",
p."SlpName",
r."ItemCode",
r."LineTotal" AS 行金额,
o."cntctcode",
o."numatcard",
o."U_consignee" AS Ord_U_consignee,
o."U_shtel" AS Ord_U_shtel
FROM SINO_SAP.dbo."ORDR" o
LEFT JOIN SINO_SAP.dbo."RDR1" r
ON o."DocEntry" = r."DocEntry"
LEFT JOIN SINO_SAP.dbo."OSLP" p
ON o."SlpCode" = p."SlpCode"
WHERE r."LineStatus" = 'o'
AND o."CANCELED" = 'n'
),
OrderContacts AS (
SELECT o."CardName", oc.name AS ContactName, oc.tel1 AS ContactPhone, 'OCPR' AS ContactSource
FROM BaseData o
INNER JOIN SINO_SAP.dbo."OCPR" oc ON o."cntctcode" = oc."cntctcode"
WHERE NULLIF(oc.tel1, '') IS NOT NULL
UNION ALL
SELECT o."CardName", oc.name AS ContactName, oc.tel2 AS ContactPhone, 'OCPR' AS ContactSource
FROM BaseData o
INNER JOIN SINO_SAP.dbo."OCPR" oc ON o."cntctcode" = oc."cntctcode"
WHERE NULLIF(oc.tel2, '') IS NOT NULL
UNION ALL
SELECT o."CardName", dl.U_consignee AS ContactName, dl.U_shtel AS ContactPhone, 'ODLN' AS ContactSource
FROM BaseData o
CROSS APPLY (
SELECT TOP 1 "U_consignee", "U_shtel"
FROM SINO_SAP.dbo."ODLN" d
WHERE o."numatcard" = d."numatcard"
ORDER BY "DocEntry" DESC
) dl
WHERE NULLIF(dl.U_shtel, '') IS NOT NULL
UNION ALL
SELECT "CardName", Ord_U_consignee AS ContactName, Ord_U_shtel AS ContactPhone, 'ORDR' AS ContactSource
FROM BaseData
WHERE NULLIF(Ord_U_shtel, '') IS NOT NULL
),
DistinctContacts AS (
SELECT DISTINCT "CardName", ContactName, ContactPhone, ContactSource
FROM OrderContacts
WHERE NULLIF(ContactName, '') IS NOT NULL
AND NULLIF(ContactPhone, '') IS NOT NULL
AND LEN(ContactPhone) <= 15 -- 过滤超长电话
),
CustomerSummary AS (
SELECT "CardName" AS 客户名称,
COUNT(DISTINCT "DocEntry") AS 合同总数,
SUM("行金额") AS 金额总数,
MAX("DocDate") AS 最近下单日期
FROM BaseData
GROUP BY "CardName"
),
TopItems AS (
SELECT "CardName", "ItemCode", SUM("行金额") AS TotalAmount,
ROW_NUMBER() OVER (PARTITION BY "CardName" ORDER BY SUM("行金额") DESC) AS ItemRank
FROM BaseData
GROUP BY "CardName", "ItemCode"
),
AggregatedItems AS (
SELECT "CardName",
STRING_AGG(CONCAT("ItemCode", '(', FORMAT(TotalAmount, 'N0'), ')'), ', ') WITHIN GROUP (ORDER BY ItemRank) AS 下单金额最多的商品top3
FROM TopItems
WHERE ItemRank <= 3
GROUP BY "CardName"
),
SalesPersons AS (
SELECT "CardName",
STRING_AGG("SlpName", ', ') WITHIN GROUP (ORDER BY TotalSales DESC) AS 销售员列表
FROM (
SELECT "CardName", "SlpName", SUM("行金额") AS TotalSales
FROM BaseData
GROUP BY "CardName", "SlpName"
) AS SalesData
GROUP BY "CardName"
)
INSERT INTO #CustomerInfo
SELECT cs.客户名称, cs.合同总数, cs.金额总数, cs.最近下单日期,
ai.下单金额最多的商品top3, sp.销售员列表,
dc.ContactName, dc.ContactPhone, dc.ContactSource
FROM CustomerSummary cs
LEFT JOIN AggregatedItems ai ON cs.客户名称 = ai."CardName"
LEFT JOIN SalesPersons sp ON cs.客户名称 = sp."CardName"
LEFT JOIN DistinctContacts dc ON cs.客户名称 = dc."CardName";
-- ========== 最终输出 ========== --
SELECT
ROW_NUMBER() OVER(ORDER BY
ISNULL(MC.毛利得分, 0) +
ISNULL(S.预收款得分, 0) +
ISNULL(S.超期得分, 0) +
ISNULL(S.库存得分, 0) DESC) AS ID,
CAST(MC.客户编号 AS VARCHAR(20)) AS 客户编号,
CAST(MC.客户类型 AS VARCHAR(20)) AS 客户类型,
CAST(LEFT(MC.客户名称, 50) AS VARCHAR(50)) AS 客户名称,
CAST(ROUND(
ISNULL(MC.毛利得分, 0) +
ISNULL(S.预收款得分, 0) +
ISNULL(S.超期得分, 0) +
ISNULL(S.库存得分, 0),
2) AS VARCHAR(50)) AS 用户评级,
CAST(MC.应收款总额 AS VARCHAR(50)) AS 应收款,
CAST(MC.预收款金额 AS VARCHAR(50)) AS 预收款,
CAST(MC.库存总额 AS VARCHAR(50)) AS 专项库存,
CAST(MC.近期毛利 AS VARCHAR(50)) AS 近期毛利,
CAST('' AS VARCHAR(100)) AS 备注2,
CAST(LEFT(CI.联系人姓名, 50) AS VARCHAR(50)) AS 联系人姓名,
CAST(LEFT(CI.联系人电话, 15) AS VARCHAR(15)) AS 联系人电话
FROM #MasterClient MC
LEFT JOIN #Scores S ON MC.客户名称 = S.客户名称
LEFT JOIN #CustomerInfo CI ON MC.客户名称 = CI.客户名称
ORDER BY ROUND(
ISNULL(MC.毛利得分, 0) +
ISNULL(S.预收款得分, 0) +
ISNULL(S.超期得分, 0) +
ISNULL(S.库存得分, 0),
2) DESC;
-- 清理所有临时表
DROP TABLE IF EXISTS #Sales;
DROP TABLE IF EXISTS #NonProjectProfit;
DROP TABLE IF EXISTS #ProjectProfit;
DROP TABLE IF EXISTS #ProfitScore;
DROP TABLE IF EXISTS #Advance;
DROP TABLE IF EXISTS #OverdueBase;
DROP TABLE IF EXISTS #Overdue;
DROP TABLE IF EXISTS #CustomerMaster;
DROP TABLE IF EXISTS #MasterClient;
DROP TABLE IF EXISTS #TotalReceivable;
DROP TABLE IF EXISTS #TotalInventory;
DROP TABLE IF EXISTS #Scores;
DROP TABLE IF EXISTS #CustomerInfo;
END id要不重复的15位整数，手机号只取符合手机号或者座机号格式的
本文介绍了 SQL 中常用的三个函数：PATINDEX 用于查找字符串首次出现的位置；NULLIF 判断两个表达式是否相等并返回相应结果；ISNULL 用于检查表达式是否为 NULL，并提供替代值。
扫一扫
1835
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
