我只有一个表-驱动程序我想知道与所有活跃的司机相比,本月司机被解雇的比例是多少。我想我已经把它弄得很复杂了,虽然它确实返回了一个结果,但它只是一个0。我试着使用小数形式的转换,但这对我来说也不起作用,因为计算结果仍然是0。
WITH X AS
(
SELECT
CAST(COUNT(*) AS DECIMAL (5,2)) TERMINATED,
0 ACTIVE
FROM DRIVER WHERE
MONTH(TERMINATION_DATE) = MONTH(CURRENT TIMESTAMP) AND YEAR(TERMINATION_DATE) = YEAR(CURRENT TIMESTAMP)
UNION ALL
SELECT
0 TERMINATED,
CAST(COUNT(*) AS FLOAT) ACTIVE
FROM DRIVER WHERE
ACTIVE_IN_DISP = 'True'
)
SELECT
CAST(SUM(TERMINATED)/(SUM(ACTIVE) + SUM(TERMINATED)*100) AS DECIMAL (10,2))
FROM X
括号有点偏离,而不是
SUM(TERMINATED)/(SUM(ACTIVE) + SUM(TERMINATED)*100)
它应该读取
SUM(TERMINATED)/(SUM(ACTIVE) + SUM(TERMINATED))*100
你的数字太小了,这就是为什么它不能放进小数点后两位的原因。
我会这样重写你的查询:
WITH begin_of_month(begin_of_month) AS (VALUES CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS),
X AS (
SELECT COUNT(*) AS terminated, 0 AS active
FROM driver
WHERE termination_date >= begin_of_month
AND termination_date < begin_of_month + 1 MONTH --index, if present, can be used!
UNION ALL
SELECT 0 AS terminated, COUNT(*) AS active
FROM driver
WHERE active_in_disp = 'True'
)
SELECT CAST(FLOAT(SUM(TERMINATED))/(SUM(ACTIVE) + SUM(TERMINATED))*100 AS DECIMAL (10,2)) --CAST only if You wish to display with 2 decimal places
FROM X