根据同一行上的其他值导出列值 [英] Derive a column value based on other on same row

问题描述

我有一个SQL查询

I have gt a SQL Query

SELECT
   [CENTRE_ID]
  , [FACULTY]
  ,SUM(CONVERT(INT ,ABS(DATEDIFF( MINUTE,CONVERT(TIME,[STARTTIME]),CONVERT(TIME,[ENDTIME]))))) AS 'TOTAL MINS PER DAY'
FROM [MISONLINE_NEW].[dbo].[BATCH_MASTER]

WHERE BATCH_STATUS LIKE 'P'
AND [STARTTIME] NOT LIKE 'ND'
GROUP BY [FACULTY], [CENTRE_ID]

，这将返回与where子句匹配的所有记录。



这里有几条记录返回结果集

, this returns all those records that matches the where clause.

Here are few records of the returned result set

CENTRE_ID    FACULTY    TOTAL MINS PER DAY
     141         2411              60
     141         2442              60
     141         2567             170
     141         2669             120

我想要第四列，可以根据每日总收入生成，将分钟转换为小时和分钟

I want a fourth column that can be generated based on TOTAL MINS PER DAY that converts Minutes into hours and Minutes

CENTRE_ID	 FACULTY    TOTAL MINS PER DAY	    TOTAL HRS PER DAY
      141         2411	            60	              1hr
      141	  2442	            60	              1hr
      141	  2567	           170	              1 hr 50 mins
      141	  2669	           120	              2 hrs

推荐答案

请参阅这些相同的链接



http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0726b6fb-c7ec-4ff3-930b-93c057c9639c/convert-minutes-to-hours?forum=sqlintegrationservices [ ^ ]



在SQL Server 2008中以HH.MM格式将分钟（整数）转换为小时数（浮点数） [ ^ ]



希望这有帮助

Refer these links which does the same

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0726b6fb-c7ec-4ff3-930b-93c057c9639c/convert-minutes-to-hours?forum=sqlintegrationservices[^]

Minutes(Integer) conversion to Hours(Float) in HH.MM format in SQL Server 2008[^]

Hope this helps

这篇关于根据同一行上的其他值导出列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！