时间小时或小时和分钟SQL Server

I need to calculate productivity.

我需要计算生产力。

Productivity = some count/ no of Hrs

I have time in HH:MM

我有时间在HH:MM

For example if I take time 1 hrs 30 minutes

例如,如果我需要时间1小时30分钟

Productivity will be

生产力将是

Productivity = some count / 1.3?

Productivity = some count / 1.5?

2 个解决方案

#1

If you have 1 hour and 30 minutes, that is 1.5 hours. You would want to take your calculation as: yourValue / 1.5 to get Productivity in units / Hour

如果你有1小时30分钟,那就是1.5小时。您可能需要将计算结果:yourValue / 1.5以单位/小时为单位获得生产力

You would likely want something like:

你可能会想要这样的东西:

SELECT DATEPART(HOUR, @yourTime) + (DATEPART(MINUTE, @yourTime) / 60.) AS pHours;

or more fully something like this, where @Time is how long the task took:

或更完全类似的东西,其中@Time是任务花了多长时间:

SELECT @workAccomplished /
   (DATEPART(HOUR, @Time) + (DATEPART(MINUTE, @Time) / 60.)) AS Productivity;

If you were dividing by 1.3, you would be calculating 1 Hour and 18 minutes, as 0.3 hours = 18 minutes.

如果你除以1.3,你将计算1小时18分钟,0.3小时= 18分钟。

#2

        declare @workaccomplished int = 240
        declare @timeworked int = (SELECT DATEDIFF(hour, '2007-05-07 09:53:01', '2007-05-08 09:53:01'))

        --datediff parameters: hour, startingtime , endtime
        declare @productivity numeric(8,4) = @workaccomplished / @timeworked

        select @productivity

#1