如何从总小时数和天数计算平均工作时数 [英] how to calculate the avg work hours from total hours and number of days
本文介绍了如何从总小时数和天数计算平均工作时数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
totalworkhr 天 empid
45:20 19 000002
32: 20 19 040015
43:21 19 040016
60:21 19 10002
这是我的餐桌全部定义varchar数据类型现在我想从总工作日和天数计算平均工作时间。
任何人都可以帮我这个。
谢谢&问候,
Ashok
解决方案
所有都定义了varchar数据类型
所以要做的第一件事是修复它。
除非是基于字符串的数据,否则不要将数据存储在字符串中:时间跨度,计数等等不是基于字符串 - 这使得做任何有用的事情最好是困难的,如果不是实际上不可能的话。
所以改变你的数据库:将totalworkhr存储为整个分钟的数字整数,或整数的微秒数。而日子也是一个整数。 (也许,也应该是int!)
那么你的计算几乎是微不足道的!
尝试如下查询
声明 @ tb table (totalworkhr varchar ( 5 ),[days] < span class =code-keyword> int ,empid varchar ( 6 ))
插入 进入 @ tb ( totalworkhr,[days],empid) values
(' 45:20', 19 ,' 000002'),
(' 32:20', <温泉n class =code-digit> 19 ,' 040015'),
(' 43:21', 19 ,' 040016'),
(' 60:21', 19 ,' 10002')
选择 totalworkhr,[天],empid,正确(' 00' + ltrim(str(((cast( left (totalworkhr, 2 ) as int )* 3600 + cast( right (totalworkhr, 2 ) as int )* 60)/ [days])/ 3600)), 2 )+ ' :' +
right (' 00' + ltrim(str((((cast( left (totalworkhr, 2 ) as int )* 3600 + cast( right (totalworkhr, 2 ) as int )* 60)/ [days])%3600)/ 60)), 2 )Avrg
来自 @ tb
totalworkhr days empid
45:20 19 000002
32:20 19 040015
43:21 19 040016
60:21 19 10002
this is my table all are defined varchar data type now i want to calculate average work hours from totalworkhrs and days .
can any one help me on this.
Thanks & Regards,
Ashok
解决方案
"all are defined varchar data type"
So the first thing to do is fix that.
Don't store data in strings unless it is string based data: and time spans, counts and so on are not string based - which makes doing anything useful with them difficult at best, if not actually impossible.
So change your database: store the totalworkhr as a number of whole minutes in an integer, or as a number of microseconds in an integer. And the days á an integer as well. (Probably, the of should be int too!)
Then your calculations become almost trivial!
try like below query
declare @tb table(totalworkhr varchar(5), [days] int, empid varchar(6)) insert into @tb (totalworkhr, [days], empid ) values ('45:20',19,'000002'), ('32:20',19,'040015'), ('43:21',19,'040016'), ('60:21',19,'10002') select totalworkhr,[days],empid, right('00'+ltrim(str(((cast(left(totalworkhr,2) as int)*3600 +cast(right(totalworkhr,2) as int)*60)/[days])/3600)),2) +':'+ right('00'+ltrim(str((((cast(left(totalworkhr,2) as int)*3600 +cast(right(totalworkhr,2) as int)*60)/[days])%3600)/60)),2) Avrg from @tb
这篇关于如何从总小时数和天数计算平均工作时数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文