Sum the results of a DateDiff [closed]

I have a table with 3 columns: SessionRole (varchar(100)), Start Date (datetime2(7)) and End Date (datetime2(7)). I need to calculate a value that is the sum of End Time – Start Time for all sessions in the table.

The table looks like this:

SessionRole  StartDate                       EndDate
Admin        2021-02-24 22:21:27.5800000    2021-02-24 22:21:27.5800000
Supervisor       2021-02-25 13:56:40.8966667    2021-02-25 14:29:07.8866667
Admin        2021-02-25 14:29:20.8866667    2021-02-25 14:29:20.8866667

I was able to get the end time – start time values in this query:

Select SessionRole,CAST(COALESCE(DATEDIFF(second, StartDate, Isnull( EndDate,getdate())),0) AS decimal(16,4))/3600 AS TotalSessionTime
from dbo.Session
Group By SessionRole, StartDate, EndDate

This gives me an output that looks like this:

0.213055555
0.000000000
0.000000000
0.000000000
0.022500000

Now I need to sum those values. I tried adding a sum to this datediff but can’t get it to work. Should this be an inner join with a query (I could not get that to work either). Any direction would be great.

Here is what I tried with sum – just adding the sum in front of the whole datediff:

Select SessionRole,CAST(COALESCE(SUM(DATEDIFF(second, StartDate, Isnull( EndDate,getdate())),0)) AS decimal(16,4))/3600 AS TotalSessionTime
from dbo.Session
Group By SessionRole, StartDate, EndDate

I get the message: The SUM function requires 1 argument(s).

Answer

You can use CTE :

WITH CTE (SessionRole,TotalSessionTime)  
AS (  
    Select SessionRole,CAST(COALESCE(DATEDIFF(second, StartDate, Isnull( EndDate,getdate())),0) AS decimal(16,4))/3600 AS TotalSessionTime
    from dbo.Session
    Group By SessionRole, StartDate, EndDate 
    )  
SELECT sum(TotalSessionTime) as sumofsessions  
FROM CTE