将时间(0)数据类型除以INT数据类型，并在单独的列中产生总和

Question

我有一个表TimeLog和列StartTime，FinishTime，Units，ElapsedTime。我想服用ElapsedTime并除以Units，以产生UPH列。这就是我到目前为止的……

SELECT StartTime, 
  FinishTime, 
  ElapsedTime = convert(time(0),dateadd(second,datediff(second,StartTime,FinishTime),0)), 
  Units 
FROM TimeLog
StartTime   FinishTime  ElapsedTime Units
08:00:00    08:25:00    00:25:00    0
08:25:00    09:15:00    00:50:00    0
09:55:00    12:32:00    02:37:00    55
12:32:00    14:31:00    01:59:00    23
12:32:00    13:55:00    01:23:00    55

希望将结果视为

StartTime    FinishTime  ElapsedTime Units  UPH
08:00:00     08:25:00    0:25:00 0  (ElapsedTime/Units)
08:25:00     09:15:00    0:50:00 0  (ElapsedTime/Units)
09:55:00     12:32:00    2:37:00 55 (ElapsedTime/Units)
12:32:00     14:31:00    1:59:00 23 (ElapsedTime/Units)
12:32:00     13:55:00    1:23:00 55 (ElapsedTime/Units)

Answer 1

使用nullif将0单元更改为null。
除非除外，从而避免了Divide by zero error encountered错误。

乘以Float 1.0，以将您从日期化的flot更改为float。

并使用isnull将零的更改为0。

然后将其全部施放在十进制中，该小数是隐式的。

-- Using a table variable for the test
declare @TimeLog table (StartTime TIME, FinishTime TIME, Units int);
insert into @TimeLog (StartTime, FinishTime, Units) values
('08:00:00','09:00:00',60),
('08:00:00','10:00:00',60),
('08:00:00','08:25:00',0),
('08:25:00','09:15:00',0),
('09:55:00','12:32:00',55),
('12:32:00','14:31:00',23),
('12:32:00','13:55:00',55);
SELECT 
  StartTime, FinishTime, 
  ElapsedTime = convert(time,dateadd(second,datediff(second,StartTime,FinishTime),0)),
  Units,
  AverageUnitsPerMinute = cast(isnull(Units / nullif(datediff(minute,StartTime,FinishTime)*1.0,0),0.0) as decimal(10,4)),
  AverageHoursPerUnit = cast(isnull(datediff(second,StartTime,FinishTime)/nullif(Units,0),0)/3600.0 as decimal(10,4)),
  AverageMinutesPerUnit = cast(isnull(datediff(millisecond,StartTime,FinishTime)/nullif(Units,0),0)/60000.0 as decimal(10,2))
FROM @TimeLog;

平均hoursperunit：
将秒数除以3600.0(60分钟*60秒(以获取小时。