Search code examples
sqlsql-serversql-server-2014

sum time with specific delimiter

Right now I have a problem with sum time based on specific condition. For example, I have something like this.

enter image description here

Due to some reason, I have to add the work time based on their activity date if only approval status on the activity date is approve.

So for the restriction example I have something like this 

-----------------------------------------------
| Activity Date   | ApprovalStatus | WorkTime |
-----------------------------------------------
| 2017-01-06      | Rejected       | 01:00:00 |
-----------------------------------------------
| 2017-01-06      | Approve        | 03:00:00 |
-----------------------------------------------
| 2017-01-06      | Waiting        | 02:00:00 |
-----------------------------------------------
| 2017-01-06      | Approve        | 01:00:00 |
-----------------------------------------------

From those example, the accepted worktime that only will be summed from this circumstances, So the expected result is become like below. The expected result is become 04:00:00 since only the approve counted for final result.

-----------------------------------------------
| Activity Date   | ApprovalStatus | WorkTime |
-----------------------------------------------
| 2017-01-06      | Approved       | 04:00:00 |
-----------------------------------------------

Is there any enlightenment to solve this problem? PS: I am using SQL Server 2014. Hope you can help me, thank you!!

Solution

  • Try like below

    Schema:

    SELECT * INTO #TAB FROM( 
SELECT '2017-01-06' AS Activity_Date 
, 'Rejected' AS ApprovalStatus 
, '01:00:00' AS WorkTime
UNION ALL 
SELECT '2017-01-06' , 'Approve' , '03:00:00' 
UNION ALL 
SELECT '2017-01-06' , 'Waiting' , '02:00:00' 
UNION ALL 
SELECT '2017-01-06' , 'Approve' , '01:00:00' 
)A

    Now Sum the Hours column by grouping the Date

    SELECT [Activity_Date]
,CAST(DATEADD(HH,SUM( DATEDIFF(HH,'00:00:00',WorkTime)),'00:00:00') AS TIME(0))
FROM #TAB 
WHERE ApprovalStatus='Approve'
GROUP BY [Activity_Date]

    Result:

    +---------------+------------------+
| Activity_Date | (No column name) |
+---------------+------------------+
| 2017-01-06    | 04:00:00         |
+---------------+------------------+

    UPDATE :

    The SUM function will only take exact numeric or approximate numeric data type . It won't accept date or Time datatype for summation.

    It is documented in SUM (Transact-SQL) on microsoft website.

    SUM ( [ ALL | DISTINCT ] expression )

    expression

    Is a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

    So you can only have a chance to write your own logic to get the sum of Time. This below will calculate the SUM of time upto milliseconds.

    SELECT [Activity_Date]
,CAST(DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', WorkTime)), '00:00:00.000') as time(0)) 
FROM #TAB2 
WHERE ApprovalStatus='Approve'
GROUP BY [Activity_Date]