时间戳

首页 > 解决方案 > 我得到除以 0，我怎样才能使这个查询更健壮？

sql - 我得到除以 0，我怎样才能使这个查询更健壮？

问题描述

我相信下面的划分是导致 null 的原因，我该如何使用 isNull 之类的东西，返回 0？

下面的查询每天获取总净值，并将值归因于周六、周日和工作日的一周中的某一天。这个想法是根据工作日或周六或周日获得平均净额。

但由于这仍然是 8 月初，还没有计算周六或周日，所以除以 0 来获得平均值反而会导致这个错误，我该如何更好地处理这个问题？

--@DespatchFrom = '08-01-2021'
--@DespatchFrom = '08-04-2021'

Select E_SAT.SATNet/E_SAT.SATCount As E_SAT,
E_SUN.SUNNet/E_SUN.SUNCount AS E_SUN,
E_WK.WKNet/E_WK.WKCount AS E_WK
from
(select DateDiff(ww, @DespatchFrom, @DespatchTo) as SATCount, 
SUM(CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END) as SATNet
                from trans t
                where d t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= DateAdd(d,1,(@DespatchTo))
                and t.cancelleddocket = '0' and t.trantype <> 'DIV'
and DatePart("w",t.Despatchdatetime) = 7)
AS E_SAT,

(select DateDiff(ww, @DespatchFrom, @DespatchTo) as SUNCount, 
SUM(CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END) as SUNNet
                from trans t
                where t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= DateAdd(d,1,(@DespatchTo))
                and t.cancelleddocket = '0' and t.trantype <> 'DIV'
and DatePart("w",t.Despatchdatetime) = 1)
AS E_SUN,

(select 
   (DATEDIFF(dd, @DespatchFrom, @DespatchTo) + 1)
  -(DATEDIFF(wk, @DespatchFrom, @DespatchTo) * 2)
  -(CASE WHEN DATENAME(dw, @DespatchFrom) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @DespatchTo) = 'Saturday' THEN 1 ELSE 0 END) as WKCount
  , SUM(CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END) as WKNet
                from trans t
                where t.DespatchDateTime >= (@DespatchFrom) and t.despatchDateTime <= DateAdd(d,1,(@DespatchTo))
                and t.cancelleddocket = '0' and t.trantype <> 'DIV'
and ((DatePart("w",t.Despatchdatetime) > 1) AND (DatePart("w",t.Despatchdatetime) < 7)))
AS E_WK

标签： sqlreporting-servicesssrs-2012divide-by-zero

解决方案

如果您希望计算结果为零，那么我将使用 CASE 语句，例如：

CASE WHEN E_SAT.SATCount = 0 THEN 0 
ELSE E_SAT.SATNet/E_SAT.SATCount END As E_SAT

推荐阅读