如何获取不同代码的总时间百分比

Question

我有一个名为“时间表”的表，数据如下所示：

Hours   Code    FiMonth  Mgr
16      OT      2011M1   Sam
0.25    DT      2011M1   Peter
1.75    HNE     2011M1   george
6.5     DT      2010M1   Peter
3.25    OT      2010M1   Sam
0.5     DT      2010M2   Sam
30      HNE     2011M1   John

现在我必须计算时间％，获取该值的公式是：

时间% = 总和(DT+OT) 小时/(DT+OT+HNE) 小时

我将查询编写为：

SELECT  ( ( SELECT  SUM(CONVERT(FLOAT, [Hours])) AS [Hours]
            FROM    Timetable d
            WHERE   d.code IN ( 'OT','DT') 
          )
          / ( SELECT    SUM(CONVERT(FLOAT, [Hours])) AS [Hours]
              FROM      Timetable t
              WHERE     t.code IN ( 'OT', 'DT','HNE')
           ) ) AS Time%
           FROM    Timetable
           GROUP BY Mgr

我必须在我的存储过程中使用这个值，并且我有很多这样的列。因此，我不想为一栏编写这么多代码。

有更好的方法吗？

Answer 1

WITH Timetable([Hours],code,FiMonth,Mgr) as
(
SELECT 16,'OT','2011M1','Sam' UNION ALL
SELECT 0.25,'DT','2011M1','Peter' UNION ALL
SELECT 1.75,'HNE','2011M1','george' UNION ALL
SELECT 6.5,'DT','2010M1','Peter' UNION ALL
SELECT 3.25,'OT','2010M1','Sam' UNION ALL
SELECT 0.5,'DT','2010M2','Sam' UNION ALL
SELECT 30,'HNE','2011M1','John'
)
SELECT Mgr,
       SUM(CASE
             WHEN code IN ( 'OT', 'DT' ) THEN CAST([Hours] AS FLOAT)
             ELSE 0
           END)/ NULLIF(SUM(CAST([Hours] AS FLOAT)),0) AS [Time%]
FROM   Timetable t
WHERE  code IN ( 'OT', 'DT', 'HNE' )
GROUP  BY Mgr