WITH
/*******销售金额******/
T_XS AS(
SELECT
TO_CHAR(WADAT_IST,'YYYY') YEARS,
TO_CHAR(WADAT_IST,'YYYYMM') MONTHS,
CASE
WHEN TO_CHAR(WADAT_IST,'YYYY')>= '2025' THEN kv.NAME_ORG4
ELSE zvj.NAME_ORG4
END AS NAME_ORG4,
CASE
WHEN TO_CHAR(WADAT_IST,'YYYY')>= '2025' THEN kv.vkbur
ELSE zvj.VKBUR
END VKBUR,
CASE
WHEN TO_CHAR(WADAT_IST,'YYYY') = TO_CHAR(SYSDATE, 'YYYY')
AND TO_CHAR(WADAT_IST,'YYYYMM') = TO_CHAR(MAX(WADAT_IST) OVER (PARTITION BY TO_CHAR(WADAT_IST,'YYYY')), 'YYYYMM')
THEN SUM(BWBJE) / EXTRACT(DAY FROM LAST_DAY(TO_DATE(WADAT_IST,'YYYYMMDD')))
* EXTRACT(DAY FROM LAST_DAY(TO_DATE(WADAT_IST,'YYYYMMDD')))
ELSE SUM(BWBJE)
END AS BWBJE
FROM Z_V_JHMX ZVJ
LEFT JOIN z_v_knvv kv
ON zvj.mandt = kv.mandt
AND LTRIM(zvj.kunnr, 0) = kv.kunnr
WHERE
WADAT_IST BETWEEN
TO_DATE('${I_YEARS}', 'YYYY') - INTERVAL '1' YEAR -- 上一年度第一天
AND LAST_DAY(TO_DATE('${I_YEARS}', 'YYYY')) -- 本年度最后一天
GROUP BY
TO_CHAR(WADAT_IST,'YYYY'),
TO_CHAR(WADAT_IST,'YYYYMM'),
CASE WHEN TO_CHAR(WADAT_IST,'YYYY')>= '2025' THEN kv.NAME_ORG4 ELSE zvj.NAME_ORG4 END,
CASE WHEN TO_CHAR(WADAT_IST,'YYYY')>= '2025' THEN kv.vkbur ELSE zvj.VKBUR END
HAVING
ROUND(SUM(BWBJE), 2) > 0),
T_RW AS(
/******月目标*********/
SELECT
YEARS,
YEARS || MONTHS MONTHS,
TYPES,
VKBUR,
NAME_ORG4,
ROUND(SALES_TASK, 2) AS SALES_TASK
FROM
CUST_USER_ROLE_ADMIN.ZT_CUSTORMERORG_TASK ZST
WHERE
ZST.TYPES = 'M'
AND NULLIF(SALES_TASK,0)<> 0
/* ${if(isnull(VKBUR),"","AND VKBUR in ('" + REPLACE(VKBUR, ',', "'" + ',' + "'")+ "')")}*/
AND ZST.YEARS = '${I_YEARS}'
ORDER BY NULLIF(SALES_TASK,0)
),
Y_RW AS(
/******年目标*******/
SELECT
YEARS,
TYPES,
VKBUR,
SUM(SALES_TASK) SALES_TASK,
NAME_ORG4
FROM
CUST_USER_ROLE_ADMIN.ZT_CUSTORMERORG_TASK
WHERE
TYPES = 'Y'
AND YEARS = '${I_YEARS}'
/*${if(isnull(VKBUR),"","AND VKBUR in ('" + REPLACE(VKBUR, ',', "'" + ',' + "'")+ "')")}*/
GROUP BY
YEARS,
TYPES,
VKBUR,
NAME_ORG4
)
SELECT
PP.NAME_ORG4 || PP.VKBUR FZ ,
PP.YEARS,
PP.MONTHS,
PP.VKBUR,
PP.NAME_ORG4,
a.BWBJE,
C.SALES_TASK AS Y_TASK ,
b.SALES_TASK,
A_TQ.BWBJE AS TQBWBJE,
a.BWBJE / CASE
WHEN b.SALES_TASK = 0 THEN NULL
ELSE b.SALES_TASK
END wcl
FROM(
/********/
SELECT
P2.vkbur,
P2.NAME_ORG4,
P2.YEARS,
P2.YEARS || P3.MONTHS AS MONTHS
FROM
(
SELECT
DISTINCT vkbur,
NAME_ORG4,
YEARS
FROM(
SELECT DISTINCT vkbur,NAME_ORG4,YEARS
FROM T_RW
UNION ALL
SELECT DISTINCT vkbur,NAME_ORG4,YEARS
FROM T_XS
)P1
WHERE
YEARS = '${I_YEARS}'
)P2
CROSS JOIN (
-- 生成1到12的整数
SELECT
RIGHT('0' || generated_period_start,2) AS MONTHS
FROM
SERIES_GENERATE_DECIMAL(1,1,13)
)P3)PP
/*****月任务****/
LEFT JOIN T_RW B
ON PP.vkbur = B.vkbur
AND PP.NAME_ORG4 = B.NAME_ORG4
AND PP.MONTHS = B.MONTHS
AND PP.YEARS = B.YEARS
/******交货金额*****/
LEFT JOIN T_XS A
ON A.MONTHS = PP.MONTHS
AND A.vkbur = PP.vkbur
AND A.NAME_ORG4 = PP.NAME_ORG4
AND A.YEARS = PP.YEARS
/******同期交货金额*******/
LEFT JOIN T_XS A_TQ
ON TO_VARCHAR(ADD_YEARS(A_TQ.MONTHS, 1), 'YYYYMM') = PP.MONTHS
AND A_TQ.vkbur = PP.vkbur
AND A_TQ.NAME_ORG4 = PP.NAME_ORG4
AND (A_TQ.YEARS + 1)= PP.YEARS
/*****年任务******/
LEFT JOIN Y_RW C
ON PP.vkbur = C.vkbur
AND PP.NAME_ORG4 = C.NAME_ORG4
ORDER BY
--PP.MONTHS ASC,
TO_INT(C.SALES_TASK) DESC,
PP.MONTHS ASC,
a.BWBJE desc 逻辑要求变更为同期销售金额计算截至到今天
本文介绍了一种使用SQL进行数值格式化的方法，包括如何将数字四舍五入到特定格式，并展示了如何利用TO_CHAR函数实现数值的精确显示。通过具体的SQL语句示例，读者可以学习到如何在数据库操作中精确控制数字的显示形式。
扫一扫
2423
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
