– 定义动态日期范围
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.客户名称,
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.客户名称;
– 1. 计算总毛利（非项目+项目）
IF OBJECT_ID(‘tempdb…#NonProjectProfit’) IS NOT NULL DROP TABLE #NonProjectProfit;
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.客户名称;
IF OBJECT_ID(‘tempdb…#ProjectProfit’) IS NOT NULL DROP TABLE #ProjectProfit;
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 客户名称;
IF OBJECT_ID(‘tempdb…#ProfitScore’) IS NOT NULL DROP TABLE #ProfitScore;
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.客户名称;
– 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.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 (‘上海办’,‘生命科学部’,‘分销部’,‘工业部’,‘天津办’,‘基础科研部’,‘沈阳办’,‘烟台办’,‘广州办’,‘成都办’,‘商务部’)
)
SELECT
[客户名称],
SUM(CASE WHEN Canceled = ‘N’ THEN [未清金额] ELSE 0 END) AS 预收款金额
INTO #Advance
FROM CTE_Result
GROUP BY [客户名称];
– 3. 计算应收款和超期应收款
IF OBJECT_ID(‘tempdb…#OverdueBase’) IS NOT NULL DROP TABLE #OverdueBase;
SELECT
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.业务体系 = ‘经销体系’;
– 完整应收款 (用于展示)
IF OBJECT_ID(‘tempdb…#TotalReceivable’) IS NOT NULL DROP TABLE #TotalReceivable;
SELECT
客户名称,
SUM(未收款金额) AS 应收款总额
INTO #TotalReceivable
FROM #OverdueBase
WHERE 项目合同 = ‘N’
GROUP BY 客户名称;
– 超期应收款 (用于得分计算)
IF OBJECT_ID(‘tempdb…#Overdue’) IS NOT NULL DROP TABLE #Overdue;
SELECT
客户名称,
SUM(未收款金额) AS 超期应收款
INTO #Overdue
FROM #OverdueBase
WHERE
项目合同 = ‘N’
AND DATEDIFF(DAY, 销货日期, @EndDate) >= 180
GROUP BY 客户名称;
– 4. 计算库存
– 完整库存 (用于展示)
IF OBJECT_ID(‘tempdb…#TotalInventory’) IS NOT NULL DROP TABLE #TotalInventory;
SELECT
[客户名称],
SUM([含税总价RMB]) AS 库存总额
INTO #TotalInventory
FROM (
SELECT
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 R0.CardName IS NOT NULL
) AS SubQuery
GROUP BY [客户名称];
– 5. 获取客户主数据
IF OBJECT_ID(‘tempdb…#CustomerMaster’) IS NOT NULL DROP TABLE #CustomerMaster;
SELECT
CardCode AS 客户编号,
CardName AS 客户名称,
CASE WHEN UPPER(ISNULL(cntctprsn, ‘’)) = ‘VIP’ THEN ‘VIP’ ELSE ‘’ END AS 客户类型
INTO #CustomerMaster
FROM OCRD
WHERE Cardtype = ‘C’;
– 6. 创建主客户表
IF OBJECT_ID(‘tempdb…#MasterClient’) IS NOT NULL DROP TABLE #MasterClient;
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.客户名称;
– 7. 计算得分 (修复列不明确问题)
IF OBJECT_ID(‘tempdb…#Scores’) IS NOT NULL DROP TABLE #Scores;
SELECT
MC.客户名称, – 明确指定使用#MasterClient表的客户名称
– 预收款得分
(ISNULL(预收款占比得分, 0) + ISNULL(预收款金额得分, 0)) / 2 * 0.2 AS 预收款得分,
– 超期应收款得分
(ISNULL(超期占比得分, 100) + ISNULL(超期金额得分, 100)) / 2 * 0.3 AS 超期得分,
– 库存得分
CASE
WHEN ISNULL(库存占比得分, 100) < ISNULL(库存金额得分, 100)
THEN ISNULL(库存占比得分, 100)
ELSE ISNULL(库存金额得分, 100)
END * 0.2 AS 库存得分
INTO #Scores
FROM #MasterClient MC
LEFT JOIN #Sales S ON MC.客户名称 = 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 预收款金额得分,
– 超期应收款得分计算
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 超期金额得分,
– 库存得分计算
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;
– 8. 汇总最终结果
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, #TEMP_Department, #OverdueBase,
#Overdue, #CustomerMaster, #MasterClient,
#TotalReceivable, #TotalInventory, #Scores;
这个是我目前的代码，现在需要将这个结果利用fdl推到ip:192.168.3.1 3306
id=lpsoft
password=@Aa.1234
database=OrderManage 他的T_Customer表中。具体的字段如下 序号 显示字段 表名 字段名 类型 数据类型 必填 默认值 说明
1 主键Id T_Customer Id 整数数据类型 bigint 是 无 15位的不重复随机整数
1 SAP客户编号 T_Customer S6 文本 varchar(50) 否 无
2 客户名称 T_Customer Name 文本 varchar(50) 否 无
3 电话 T_Customer Phone 文本 varchar(15) 否 无
4 电话2 T_Customer Phone2 文本 varchar(15) 否 无
5 客户类型 T_Customer CustomerTypeCode 下拉：普通/VIP varchar(20) 否 普通=703192237846597 VIP=703192237850693
6 用户评级 T_Customer S5 文本 varchar(50) 否 无
7 应收款 T_Customer S1 文本 varchar(50) 否 无
8 预收款 T_Customer S2 文本 varchar(50) 否 无
9 专项库存 T_Customer S3 文本 varchar(50) 否 无
10 近期毛利 T_Customer S4 文本 varchar(50) 否 无
11 备注 T_Customer Remark 长文本 text 否 无
帮我写个代码完成推送，id例如20250731103042（时间部分） + 7（随机数） → 202507311030427 帮我形成完整的代码
本文介绍了一个SQL存储过程，用于生成8位随机数，并展示了如何将此随机数与当前日期时间结合，形成一个唯一标识符。这种方法适用于需要创建唯一记录的应用场景。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
