我在使用fdl的时候，数据来源代码为
– 定义动态日期范围
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;
然后报错了com.fr.dp.exception.FineDPException: 数据连接异常 - DataBase[sino_sap] get column failed - SQL语句包含非法字符 - JDBC SQL校验语句中出现了不被允许的关键字: create 可以帮我把代码重构么 不要使用with里面还套用with的语法
最新发布
文章讲述了在帆软报表中遇到SQL片段在命令行正常但在预览时出错的情况，建议用户在数据源编辑器中手动输入字段和逻辑，因为版本9存在这个问题，这能确保报表正常工作。
扫一扫
3472
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
