如何将“分钟数”存储为小时 [英] how to store "count of minute' into hours
本文介绍了如何将“分钟数”存储为小时的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
选择 distinct (Emp_Status),
count (Att_Mints / 60) as OTHOUR,
COUNT(Att_Totalmints / 60) as ProductionHours
来自 Attendence
inner join EmployeeMaster on fk_Att_EmpCode = fk_Att_EmpCode
其中年(Att_Date)= ' 2014' 和月(Att_Date)= ' 1'
group by Emp_Status
以上查询计数
Att_Mints
= 150
和计数
Att_Total mints
是120
i想要输出OTHOURS = 1:30min
和
ProductionHours
= 2小时
请帮帮我
解决方案
有几种方法可以做到这一点。
如果你能在你的应用程序中处理它会更好但是无论如何你就是
选择 distinct (Emp_Status),
count (Att_Mints / 60) as OTHOUR,
cast(count(Att_Mints)/ 60 as varchar ( 3 ))+
' :' +
cast(count(Att_Mints)% 60 as varchar ( 3 ))
as OTHOUR2,
COUNT(Att_Totalmints / 60)< span class =code-keyword> as ProductionHours
from Attendence
inner join EmployeeMaster on fk_Att_EmpCode = fk_Att_EmpCode
其中年(Att_Date)= ' 2014' 和月(Att_Date)= ' 1'
group by Emp_Status
如果您要填充它,您可以使用以下内容:
cast(count(Att_Mints)/ 60 as varchar ( 3 ))+
: +
right (' 0' + CAST(count(Att_Mints)% 60 as varchar ( 2 )) , 2 )
as OTHOURPad,
plz检查此查询。
选择 distinct (Emp_Status),
right (' 00' + cast((count(Att_Mints / 60)/ 60 ) as varchar ( 2 )), 2 )+
CASE (强制转换((count(Att_Mints / 60)% 60 ) as varchar ( 2 )))何时 0 然后 ' 小时'
else (' < span class =code-string>:' + right (' 00' + cast((count(Att_Mints / 60)% 60 ) as varchar ( 2 )), 2 )+ ' min') end as OTHOUR,
正确(' 00' + cast((count(Att_Totalmints) / 60)/ 60 ) as varchar ( 2 )), 2 )+
CASE (cast((count(att_Totalmints / 60)% 60 ) as varchar ( 2 ))) 0 然后 ' 小时'
else (' :' + right (' 00' + cast((count(Att_Totalmints / 60) )% 60 ) as varchar (< span class =code-digit> 2 )), 2 )+ ' < span class =code-string> min')
end as ProductionHours
来自 Attendence
inner 加入 EmployeeMaster on fk_Att_EmpCode = fk_Att_EmpCode
其中年(Att_Date)= ' 2014' 和月(Att_Date)= < span class =code-string>' 1'
group by Emp_Status
SELECT ISNULL(NULLIF( (右('000'+ CAST(@ Minutes / 60 AS VARCHAR(32)),3)+':'+ RIGHT('0'+ CAST(@Minutes%60 AS VARCHAR(32)),2)), '000:00'), '')
select distinct(Emp_Status),
count ( Att_Mints/60) as OTHOUR,
COUNT (Att_Totalmints /60 ) as ProductionHours
from Attendence
inner join EmployeeMaster on fk_Att_EmpCode=fk_Att_EmpCode
where year(Att_Date)='2014' and Month (Att_Date)='1'
group by Emp_Status
in above query Count of
Att_Mints
is =150
and count of
Att_Totalmints
is 120
i want output of OTHOURS=1:30min
and
ProductionHours
=2 hour
please help me
解决方案
There are several ways to do that.
if you could handle it in your application it would be better but anyways there you are
select distinct(Emp_Status), count ( Att_Mints/60) as OTHOUR, cast(count(Att_Mints) / 60 as varchar(3))+ ':'+ cast(count(Att_Mints) % 60 as varchar(3)) as OTHOUR2, COUNT (Att_Totalmints /60 ) as ProductionHours from Attendence inner join EmployeeMaster on fk_Att_EmpCode=fk_Att_EmpCode where year(Att_Date)='2014' and Month (Att_Date)='1' group by Emp_Status
in the case that you want to pad it you could use the following :
cast(count(Att_Mints) / 60 as varchar(3))+ ':'+ right('0' + CAST(count(Att_Mints) % 60 as varchar(2)) ,2) as OTHOURPad,
plz check this query.
select distinct(Emp_Status), right('00'+cast((count ( Att_Mints/60) / 60) as varchar(2)),2) + CASE (cast((count ( Att_Mints/60) % 60) as varchar(2))) when 0 then ' Hour' else (':' + right('00'+cast((count ( Att_Mints/60) % 60) as varchar(2)),2)+' min') end as OTHOUR, right('00'+cast((count ( Att_Totalmints/60) / 60) as varchar(2)),2) + CASE (cast((count ( Att_Totalmints/60) % 60) as varchar(2))) when 0 then ' Hour' else (':' + right('00'+cast((count ( Att_Totalmints/60) % 60) as varchar(2)),2)+' min') end as ProductionHours from Attendence inner join EmployeeMaster on fk_Att_EmpCode=fk_Att_EmpCode where year(Att_Date)='2014' and Month (Att_Date)='1' group by Emp_Status
SELECT ISNULL(NULLIF((RIGHT('000'+CAST(@Minutes/60 AS VARCHAR(32)),3) +':'+ RIGHT('0'+CAST(@Minutes % 60 AS VARCHAR(32)),2)),'000:00'),'')
这篇关于如何将“分钟数”存储为小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文