How to calculate overal total of DATEDIFF in HH:MM in MS SQL

Question

Any help you can provide would be extremely useful! Please note, this is my first attempt at creating a test table to post a question...I hope its correct! I have the following query that counts each incident and disposition grouped by CallSign. It also does a calculation on what percentage of the total incidents resulted in an air support or transport to a hospital.

WITH CTE AS  
(  
SELECT DISTINCT  
    iu.CallSign  
    ,COUNT (DISTINCT  iu.IncidentNumber) as TotalIncidents  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('FC'))then iu.IncidentNumber END) as FC  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('FREF'))then iu.IncidentNumber END) as FREF  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('FAA'))then iu.IncidentNumber END) as FAA  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('NCNV'))then iu.IncidentNumber END) as NCNV  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('PA'))then iu.IncidentNumber END) as PA  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('COS'))then iu.IncidentNumber END) as COS  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('EREF'))then iu.IncidentNumber END) as EREF  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('CAS'))then iu.IncidentNumber END) as CAS	  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('CEN'))then iu.IncidentNumber END) as CEN  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('CBE'))then iu.IncidentNumber END) as CBE	  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('MON'))then iu.IncidentNumber END) as MON	  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('NFR'))then iu.IncidentNumber END) as NFR  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('MA'))then iu.IncidentNumber END) as MA	  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('UNF'))then iu.IncidentNumber END) as UNF  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('SD'))then iu.IncidentNumber END) as SD	  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('CT'))then iu.IncidentNumber END) as CT	  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('tra1','tra2','tra3'))then iu.IncidentNumber END) as TRA  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('tota'))then iu.IncidentNumber END) as TOTA  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('EAA'))then iu.IncidentNumber END) as EAA  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('EC'))then iu.IncidentNumber END) as EC  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('leo'))then iu.IncidentNumber END) as LEO  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('SDBC'))then iu.IncidentNumber END) as SDBC  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('TOTJ'))then iu.IncidentNumber END) as TOTJ  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('TOTM'))then iu.IncidentNumber END) as TOTM  
    ,COUNT (DISTINCT CASE WHEN  (iu.Disposition1 IN ('TOTA','TRA1','TRA2','TRA3'))then iu.IncidentNumber END) as TOTALTRA  
    ,CONVERT (VARCHAR(20),SUM (DATEDIFF(Minute,DispatchTime, ClearTime))/60)+'.'+   
     RIGHT('00'+ CONVERT(VARCHAR(2), SUM (DATEDIFF(Minute,DispatchTime, ClearTime)) % 60),2) AS Utilization3  
    ,SUM(iu.DispatchTimeToClearedTimeInS) as Utilization5  
FROM  MV_IncidentUnits iu   
WHERE  
  iu.IncidentDate >=@IncidentDate  
  AND iu.IncidentDate <=@IncidentDate2  
  AND iu.AgencyId  IN (@Agency)  
 AND iu.Callsign (@Callsign)  
 GROUP BY CallSign   
)  
SELECT  
*  
,(TOTALTRA) * 100.00 / (TotalIncidents) AS 'Transport Percent'  
FROM CTE  
GROUP BY cte.CallSign, cte.TotalIncidents,cte.Utilization3,cte.Utilization5, cte.TRA,  cte.totaltra, cte.FREF, cte.PA, cte.FAA, cte.ncnv, cte.pa, cte.ct, cte.fc, CTE.CAS, CTE.CBE, CTE.CEN, CTE.MON, CTE.NFR, CTE.COS, CTE.MA, CTE.UNF, CTE.SD, CTE.EREF, CTE.TOTA, CTE.EAA,CTE.EC, CTE.LEO, CTE.SDBC, CTE.TOTJ, CTE.TOTM  
Order By CallSign

I'm trying to determine what is the best way to get a total per CallSign of the DATEDIFF between DispatchTime and ClearTime. 'Utilization3' appears to do what I'm needing, but I'm finding several places online that say it could calculate incorrectly. Secondly, I need a way to get an overall total of Utilization 3 for all CallSigns. I tried to do this with an expression in Report Builder with =sum(Fields!Utilization3.Value) and received an error.

The above query provides results like this:

An alternate option would be to utilize another column I have called DispatchTimeToClearedTimeInS. This column already provides the difference between the DispatchTime and ClearTime in SECONDS. I have included a sum of this column grouped by CallSign in the query results sample and the test table. I would like it to be formatted in HH.MM, where the hours would continue to go up (IE: '157.35' aka 157 hours and 35 minutes).

THANK YOU!

Answer

If you want the difference in time in minutes, then using datediff with seconds should be OK. The problem is if you want the difference in hours, and you use datediff with HOUR, because datediff counds boundaries so it thinks that 14:01 to 15:59 is one hour and so is 14:59 to 15:01.

As for the formatting, that may best be done in the client; as I don't know Reporting Services, I cannot assist with that. But you can convert the value to the time data type as:

dateadd(second, datediff(....), convert(time(0), '00:00:00'))

And it is probably easier to format this value to HH.SS in Reporting Services than the raw seconds.

Answer

Hi @Tamayo, Ashley ，

Thank you for providing the relevant tables and data, but it seems that the table you provided is incomplete, and I cannot successfully execute the CTE you provided.

Please try:

select sum(datediff(hour,DispatchTime,ClearTime)) over(partition by CallSign)  
from yourtable

I'm sorry that I am not familiar with SSRS. Please post the formatting question on the SSRS forum.

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

How to calculate overal total of DATEDIFF in HH:MM in MS SQL

2 answers