Getting the Sum of a Datediff Result

Getting the sum of a datediff result

If you include any other columns, you'll need to also include a GROUP BY clause

SELECT     AnotherColumn, SUM(DATEDIFF(day, StartDate, EndDate)+1) AS myTotal
FROM         myTable
WHERE      (Reason = '77000005471247')
GROUP BY   AnotherColumn

Get the SUM of a DATEDIFF result

Few adjustments

• Extra GROUP BY using a derived table
• Removed unnecessary casts to a string
• Formatting...

SQL:

WITH cte
AS (SELECT ROW_NUMBER() OVER (ORDER BY Date) AS ID,
           Die_ID,
           Date,
           Status
    FROM Tooling_Status
    WHERE (date      BETWEEN '2018-02-27 00:00:00' AND '2019-02-27 11:59:59'      )
          AND Date IS NOT NULL
)

SELECT SUM(d.Seconds) AS Seconds 
, d.DIA AS [Day]
FROM (
     SELECT DATEDIFF(ss, c1.Date, MIN(c2.Date)) AS Seconds,
           CAST(c1.Date AS DATE) AS DIA
     FROM cte c1
        LEFT OUTER JOIN cte c2
            ON c1.Date < c2.Date
               AND c1.Die_ID = c2.Die_ID
     WHERE c1.Status = 2 AND c2.Status = 1
     GROUP BY c1.Date
) d
GROUP BY d.DIA
ORDER BY [Day] DESC;

Sum the results of a DateDiff

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  

SQL Select Statement with DateDiff SUM and Math operation

The problem is one of scope / order of operation. The aggregate sum is not available at the time you want to do the % calculation. So you first have to generate the sum and then you can get the %.

This can be accomplished with a COMMON TABLE EXPRESSION (CTE) or a sub query.

Here's the CTE Example.

WITH CTE AS (SELECT EMPLOYEEID, SUM(DATEDIFF(day,StartDate,EndDate)) as SumDates, 
FROM dbo.[abovetable] 
GROUP BY EmployeeID)
SELECT EmployeeID, SumDates, DATEDIFF(day,StartDate,EndDate) / SumDates
FROM Employee
INNER JOIN CTE on E.EmployeeID = CTE.EmployeeID
WHERE EmployeedId=1

and here's an inline query (sub query) example

SELECT EmployeeID, SumDates, DATEDIFF(day,StartDate,EndDate) / SumDates
FROM Employee
INNER JOIN (SELECT EMPLOYEEID, SUM(DATEDIFF(day,StartDate,EndDate)) AS SumDates, 
            FROM dbo.[abovetable] 
            GROUP BY EmployeeID) CTE 
 ON E.EmployeeID = CTE.EmployeeID
WHERE EmployeedId=1

In both cases the sum of the dates is being determined before the division is occurring. In the CTE example, the CTE is materialized with data before the math.

In the case of the inline query, the order of operations is inside out so it calculates the sub table, then executes the query.

Sum datediff and group by

Try this:

SELECT T.NAME, SUM(DATEDIFF(HH,T.DATE_DUE,T.DATE_START))as Donation_Hours 
FROM TASKS T  
GROUP BY t.name
ORDER BY name 

---

Related Topics