我的TSQL来计算百分比显然是愚蠢的;这是我的存储过程逻辑的一部分:我的计算字段逻辑有什么问题(试图计算百分比)?
. . .
AVG(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)
Week1Price,
AVG(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END)
Week2Price,
AVG(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END)
-
AVG(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)
PriceVariance,
AVG(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END)
-
AVG(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)
/
NULLIF(AVG(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END),0)
PercentageOfPriceVariance
. . .
我想PercentageOfPriceVariance做的是:
(Week2Price - Week1Price)/Week1Price
例如,如果Week2Price是3.38,而Week1Price是20.27,然后(Week2Price - Week1Price)应该是-16.89;然后(-16.89)除以3.38应为-4.99。
注意,虽然,我得到的结果从疯狂不同(见第3行,其中%为2.38):
什么是错在我的计算字段的逻辑是什么?