我希望我的sql代码在运行总数中忽略0。我试图显示一个显示fuelused/wh的图表,其中包括一些我们不希望显示的延迟。我们前面有两个select语句,一个是临时工的工作时间。另一个变量拉另一个变量“累计总和燃料使用”。trackingbasisid<>4将从数据中排除所有延迟。但是当这样做的时候,我们的数据会因为延迟而跳到0。然后,我们在主select语句中组合这些内容(我省略了代码的连接/大部分,因为它所在的整个存储过程相当长)
我们在一个折线图中显示它,所以它一直降到零,我们希望代码忽略0,并且是一条稳定的线,只考虑零以上的数字/不包括延迟阶段(trackingbasis=4)。有没有关于如何忽略0的线索?到目前为止,我的解决方案是在一个更下面的语句中创建它,这样它就把0变成了空值,这种情况下它不会降到0,但看起来仍然有点奇怪/不正确。
Select SUM(md.WorkHours) OVER (ORDER BY md.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS CumulativeSumWorkHours
INTO #CumulativeLoadData
FROM V_MechanicalDaily md
WHERE
md.ProjectNumber = @ProjectNumber
and md.PrimePhaseID in (SELECT Value FROM DredgeData.fn_Split(@PrimeP, ','))
and md.Date >= @BeginDate and md.Date <= @EndDate
and md.MeasureSystemID = md.ProjUOM
[第二个临时表]
Select pltd.ProjectNumber,
pltd.PrimePhaseNumber,
pltd.date,
pltd.PlantNumber,
pltd.FuelUsed as FuelUsed,
SUM(pltd.FuelUsed) OVER (ORDER BY pltd.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS
CumulativeSumFuelUsed,
AVG(pltd.FuelUsed) OVER (ORDER BY pltd.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS
CumulativeAvgFuelUsed
INTO #FuelUsedTable
from V_PLantDetail pltd
INNER JOIN V_ProjectPrimePhase p
on p.ProjectNumber = pltd.ProjectNumber
and p.PrimePhaseNumber = pltd.PrimePhaseNumber
and p.UnitOfMeasureID = pltd.MeasureSystemID
Where
pltd.ProjectNumber = @ProjectNumber
and pltd.PrimePhaseID in (SELECT Value FROM DredgeData.fn_Split(@PrimeP, ','))
and pltd.PlantNumber = p.PlantNumber
and date >=@BeginDate
and date <=@EndDate
and pltd.SoftDelete <> 1
and p.TrackingBasisID <> 4
[在主选择语句中]
fut.CumulativeSumFuelUsed / NULLIF (cum.CumulativeSumWorkHours,0) AS CumulativeFuelUsedWH,'''
暂无答案!
目前还没有任何答案,快来回答吧!