I want to calculation MTTR.This is formula Sum(Finish_Date-Start_Date)/n I need to calculate in SQL Server.

SELECT dbo.BOYS.Machine_ID,
    SUM(DATEDIFF(SECOND, Start_Date, Finish_Date)) as Total_TTR
    , SUM(1) AS n
    , SUM(DATEDIFF(SECOND, Start_Date, Finish_Date))/SUM(1) AS MTTR
FROM [BT].[dbo].[BOYS]
GROUP BY Machine_ID  

I calculate it second type.

• MTTR
• 3600
• 165600
• ...

in table. but I want it in hh:mm:ss format.

• MTTR
• 01:25:13 etc.
• ...

I use

SELECT dbo.BOYS.Machine_ID,
    SUM(DATEDIFF(SECOND, First_Date, Second_Date)) as Total_DT
    ,SUM(1) AS n, SUM(DATEDIFF(SECOND, First_Date, Second_Date))/SUM(1) AS MDT
    , Convert(varchar(6), ((SUM(DATEDIFF(SECOND, First_Date, Second_Date))/SUM(1))/3600)
      ':'
      RIGHT('0'   CONVERT(varchar(2),(SUM(DATEDIFF(SECOND, First_Date, Second_Date)/SUM(1)) % 3600) / 60), 2)
      ':'
      RIGHT('0'   CONVERT(varchar(2),(SUM(DATEDIFF(SECOND, First_Date, Second_Date))/SUM(1)) `),2)) AS 'MTTR'
 FROM [BT].[dbo].[BOYS]
 GROUP BY Machine_ID    

but it throws an error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

CodePudding user response：

Your primary issue is this line, with spacing you can see the issue is a bracket in the wrong place, so you have SUM(1) inside the other SUM.

RIGHT(
  '0'  
  CONVERT(
    varchar(2),
    (
      SUM(
        DATEDIFF(
          SECOND,
          First_Date, 
          Second_Date
        )
        / SUM(1)
      )
      % 3600
    )
    / 60
  ),
  2
)

You can clean this up considerably.

• Firstly, put the DATEDIFF in a CROSS APPLY(VALUES so you don't have to repeat it.
• SUM(1) is the same as COUNT(*), and SUM(x) / COUNT(*) is the same as AVG(x).
• Use TIMEFROMPARTS to create a time value.
• Don't use three-part naming. Your connection should define which database you are using.
• Don't quote table and column names unless you have to. And if you do, do it with [].

SELECT
      b.Machine_ID
    , SUM(v.diff) AS Total_DT
    , COUNT(*) AS n
    , AVG(v.diff) AS MDT
    , TIMEFROMPARTS(
        AVG(v.diff) / 3600,
        (AVG(v.diff) % 3600) / 60,
        AVG(v.diff) % 60,
        0, 0) AS MTTR
FROM dbo.BOYS b
CROSS APPLY (VALUES (
    DATEDIFF(SECOND, b.First_Date, b.Second_Date)
) ) v(diff)
GROUP BY
  b.Machine_ID;

db<>fiddle

CodePudding user response：

You can put the query with the group by in a subquery, and then select on that resultset, then you don't have to worry about aggregates in the upper select.

Look at this example

first I need to create some sample data

declare @boys table (machine_id int, start_Date datetime, finish_Date datetime)

insert into @boys values 
  (1, '20220728 08:00:00', '20220728 09:15:30'),
  (1, '20220728 10:30:00', '20220728 10:45:00'),
  (2, '20220728 08:30:00', '20220728 14:12:31')

now for the query, it was not clear to me if you want to build the time for the average or for the total, so I put both in the query, just pick the one you need

select t.Machine_ID,
       t.n,
       t.Total_DT,
       t.MDT,
       
       -- time base on the average
       Convert( varchar(6), (MDT / 3600))
                  ':'
                  RIGHT('0'   CONVERT(varchar(2), (MDT % 3600) / 60), 2)
                  ':'
                  RIGHT('0'   CONVERT(varchar(2), (MDT % 60)), 2)
       as MTTR_avg,
       
       -- time base on the total
       Convert( varchar(6), (Total_DT / 3600))
                  ':'
                  RIGHT('0'   CONVERT(varchar(2), (Total_DT % 3600) / 60), 2)
                  ':'
                  RIGHT('0'   CONVERT(varchar(2), (Total_DT % 60)), 2)
       as MTTR_tot
                
from   ( SELECT b.Machine_ID,
                SUM(DATEDIFF(SECOND, b.Start_Date, b.Finish_Date)) as Total_DT,
                SUM(1) AS n, 
                SUM(DATEDIFF(SECOND, b.Start_Date, b.Finish_Date)) / SUM(1) AS MDT
         FROM   @boys b 
         group by Machine_ID
       ) t

It also makes the convert much more easy to read.

It results in this