将SUM用于varchar数据类型 [英] Use SUM for varchar data type

问题描述

我需要以HH.MM的格式计算过去30天的飞行员飞行小时数，例如（23.40），但是我的代码给了我总数作为数字而不是作为例子22.70的小时数这应该是23.10


我尝试过：

I need to calculate the SUM of pilot flights hours for last 30 days in format of HH.MM as example (23.40) but my code gave me the total as number not as hours as example 22.70 this should be 23.10

What I have tried:

ISNULL(SUM(cast(case when ActualOff>=DATEADD(DD, -30, @Date) and actualon<=@Date THEN 
		RIGHT('   ' + CONVERT(VARCHAR(12), (DATEDIFF(MINUTE, ActualOff, ActualOn))/60),3)  + '.' + RIGHT('00' +
		CONVERT(VARCHAR(12), (DATEDIFF(MINUTE, ActualOff, ActualOn)) % 60), 2) END as float)),0)				AS '30days',

推荐答案

要做到这一点，你需要将时间转换为分钟，SUM它们，然后分别使用MOD函数和整数除法和模数分别提取小时和分钟部分。然后你可以将两者结合起来得到总小时和分钟值。



但是......我不是那样做的，就像你的方式一样存储数据是完全错误的：永远不要将数值存储为字符串，始终将它们存储为数字。在这种情况下，我将它存储为整数分钟并使用它而不是基于字符串的小时和分钟。

改为那，你的SUM变得微不足道了！

To do that, you would need to convert the time to minutes, SUM them, and then extract the hour and minute portions separately with and integer divide and a modulus using the MOD function respectively. You can then combine the two to get a "total" hours and minutes value.

But...I'm not doing that for you, as the way you are storing the data is plain wrong: never store numeric values as strings, always store them as numbers. In this case, I'd store it as an integer number of minutes and use that instead of faffing with string based hours and minutes.
Change to that, and your SUM becomes trivial!

如果您在变量中得到总分钟数，您可以很容易地进行计算。

IF you come as far as to have the total number of minutes in a variable you can do your calculations pretty easily.

DECLARE @totalMinutes INT = 673;
SELECT concat(@totalMinutes / 60, '.', @totalMinutes % 60);

结果= 11.13



但是不要在VARCHAR上做你的操作。



[更新]

尝试从你的非常有意义复杂的表达。

基本上你需要先总结会议记录，然后再进行计算。

从外观来看，你是这样做的。 br />


尝试类似的东西。

Result = 11.13

But don't do your operations on VARCHAR.

[UPDATE]
Trying to make some sense out of your very complicated expression.
Basically you need to sum up the minutes first, THEN do the calculation.
You are doing it the other way around, from the looks of it.

Try something likes this.

DECLARE @totalMinutes INT;
SET @totalMinutes = SUM(CASE WHEN fl.ActualOff >= DATEADD(DD, -30, @Date) AND fl.actualon <= @Date THEN
    DATEDIFF(MINUTE, fl.ActualOff, fl.ActualOn)
END) 
SELECT CAST(CONCAT(@totalMinutes / 60, '.', @totalMinutes % 60) AS DECIMAL(10,2)) AS '30days';

再一次。在必要之前，不要将完美的数字转换为VARCHAR。

无论谁告诉你这是一个好主意是错的，它只是让事情变得混乱。

And again. Do not convert perfectly good numbers into VARCHAR until you have to.
Whoever told you that was a brilliant idea was wrong, it just makes things messy.

这篇关于将SUM用于varchar数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！