Ed Wagner (7/31/2014)
It sounds to me like you're after the number of units picked per hour for a specific period of time. It reminds me of a shop floor productivity report. Is this correct? If so, simply subtract the times and divide the quantity by the difference. This example uses the difference in minutes for precision.
with times as (select dateadd(minute, -90, getdate()) starting_time,
GETDATE() ending_time,
14 quantity
union all
select dateadd(hour, -2, getdate()) starting_time,
GETDATE() ending_time,
160 quantity)
select starting_time, ending_time, quantity,
convert(numeric(12, 6), DATEDIFF(minute, starting_time, ending_time)) / 60 hours,
round(quantity / convert(numeric(12, 6), DATEDIFF(minute, starting_time, ending_time)) * 60, 3) uph
from times;
Am I over-simplifying this?
No. But it can be made a bit simpler still using the hidden power of the DATETIME data type.
WITH TestData (StartDT,EndDT,Quantity) AS
(
SELECT DATEADD(mi,-90,GETDATE()), GETDATE(), 14 UNION ALL
SELECT DATEADD(hh,- 2,GETDATE()), GETDATE(), 160
)
SELECT StartDT
,EndDT
,Quantity
,UPH = CAST(Quantity/(CAST(EndDT-StartDT AS DECIMAL(17,12))%1*24) AS DECIMAL(9,3))
FROM TestData
;
--Jeff Moden
Change is inevitable... Change for the better is not.