如何在SQL Server中的数据透视表内将秒转换为小时分钟 [英] How to convert seconds into hours minutes inside Pivot in SQL Server

问题描述

我在sql server 2012中编写了一个数据透视查询。它运行正常，它显示了行下的用户名结果和列下的生产小时数（以秒为单位）。但我需要将秒分为几小时：分钟格式。请帮我查询。

 声明  @ empid   nvarchar （ 20 ）， @fromdate   date ， @ todate   date ， @cols   nvarchar （max）， @ query   AS   VARCHAR （MAX）， @ dt   varchar （ 20 ）， @ dt1   varchar （ 20 ）
 
  set   @ empid  = '  EC0100' 
  set   @ fromdate  = '  10/01/13' 
  set   @ todate  = '  10/31 / 13' 
  set  @ dt = '  Exceptions' 
  set  @ dt1 = ' 技术问题' 
 
 选择  @cols  = STUFF（（ SELECT   distinct  ' ，' + QUOTENAME（ convert （ nvarchar （ 20 ），c._Date， 101 ））
  FROM  MIS_BM_Calendar c 
 其中 c._Date    @ fromdate  和  @ todate  和 _Day 不   
（' 星期日'）
  FOR  XML PATH（' ）， TYPE  
）.value（' 。'，'  NVARCHAR（MAX）'）
， 1 ， 1 ， ' '）
 
  set  @ query = ' 选择UserName，' + @cols + ' 来自
（选择e.UserName，c._Date，（SUM（DATEDIFF（SS，0，c.TimeTaken）））作为TimeTaken $ b来自MIS_BM_Users的$ b e 
内部联接MIS_Opus c 
 e.EmpId = c.EmpId 
其中（e.AccountManagerID =''' + @ empid + ' ''）和c.Category不在（''' + @dt +  ' ''，''' + @ dt1 + ' ''）
 group by c._Date，e.UserName 
）As SourceTable 
 Pivot 
（
 SUM（TimeTaken）for _Date in（' + @cols + ' ）
）作为Pvt' 
 
 执行（ @ query ）

解决方案

我创建了一个标量函数并转换了秒数到HHMMSS。然后在主查询中包含该函数。

  ALTER   function  [dbo]。[ConvertSecondsToHHMMSS] 
（
  @ TotalSeconds   int  
）
 返回  nvarchar （  20 ）
 作为 
 开始 
 声明  @ hours   int ， @ minutes   int ， @ seconds   int ， @ result   nvarchar  （ 20 ）
 
  set   @ hours  =  @ TotalSeconds  /  3600  
  set   @ minutes  =（ @ TotalSeconds  ％ 3600 ）/  60  
  set   @ seconds  =  @ TotalSeconds ％ 60  
 
  set   @ result  =  CONVERT （ nvarchar （ 20 ）， @hours ）+ ' ：' +  CONVERT （ nvarchar （ 20 ）， @ minutes ）+ ' ：' + CONVERT（ nvarchar （ 20 ）， @ seconds ）
 
  return   @ result  
 结束 

主查询：

  set  @ query = ' 从
（$ b）中选择UserName，' + @cols + '  $ b选择e.UserName，c._Date，dbo.ConvertSecondsToHHMMSS（SUM（DATEDIFF（SS，0，c.TimeTaken）））作为来自MIS_BM_Users的TimeTaken e 
内部联接MIS_Opus c on e.EmpId = c.EmpId 
 where（e.AccountManagerID =''' + @ AccountManagerId + ' ''）
和c.Category不在（''' + @ Condition1 + ' ''，''' + @ condition2 + ' ''）
 group by c._Date，e.UserName 
）As SourceTable 
在（' + @cols + ' （
 MIN（TimeTaken）） -string>）
）作为Pvt' 
 
 执行（ @query ）

I have written a pivot query in sql server 2012. It works fine and it shows the result of usernames under Rows and sum of production hours in seconds under columns. But I need the seconds to be splitted into hours:minutes format. Please help me on query.

declare @empid nvarchar(20), @fromdate date, @todate date, @cols nvarchar(max), @query  AS VARCHAR(MAX), @dt varchar(20), @dt1 varchar(20)

set @empid = 'EC0100'
set @fromdate = '10/01/13'
set @todate = '10/31/13'
set @dt='Exceptions'
set @dt1='Tech Issues'

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(nvarchar(20),c._Date, 101))
         FROM MIS_BM_Calendar c
         where c._Date between @fromdate and @todate and _Day not in
('Sunday')
         FOR XML PATH(''), TYPE
         ).value('.', 'NVARCHAR(MAX)')
     ,1,1,'')

set @query= 'select UserName, '+@cols+' from
(select  e.UserName, c._Date , (SUM(DATEDIFF(SS,0,c.TimeTaken))) As TimeTaken
 from MIS_BM_Users e
 inner join MIS_Opus c
 on e.EmpId=c.EmpId
 where (e.AccountManagerID='''+@empid+''') and c.Category not in ('''+@dt+''','''+@dt1+''')
 group by c._Date, e.UserName
) As SourceTable
Pivot
(
SUM(TimeTaken) for _Date in ('+@cols+')
) As Pvt'

execute(@query)

解决方案

I have created a scalar function and converted the seconds to HHMMSS. Then include the function in the main query.

ALTER function [dbo].[ConvertSecondsToHHMMSS]
(
@TotalSeconds int
)
Returns nvarchar(20)
As
Begin
declare @hours int, @minutes int, @seconds int, @result nvarchar(20)

set @hours = @TotalSeconds / 3600
set @minutes = (@TotalSeconds % 3600) / 60
set @seconds = @TotalSeconds % 60

set @result =  CONVERT(nvarchar(20),@hours) + ':' + CONVERT(nvarchar(20),@minutes) + ':' +CONVERT(nvarchar(20),@seconds)

return @result
end

Main Query:

set @query= 'select UserName, '+@cols+' from
(
select  e.UserName, c._Date, dbo.ConvertSecondsToHHMMSS(SUM(DATEDIFF(SS,0,c.TimeTaken))) As TimeTaken from MIS_BM_Users e
inner join MIS_Opus c on e.EmpId=c.EmpId
where (e.AccountManagerID='''+@AccountManagerId+''')
and c.Category not in ('''+@Condition1+''', '''+@condition2+''')
group by c._Date, e.UserName
) As SourceTable
Pivot
(
MIN(TimeTaken) for _Date in ('+@cols+')
) As Pvt'

execute(@query)

这篇关于如何在SQL Server中的数据透视表内将秒转换为小时分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！