CODEWITHSUNDEEP
sqldatesql-server-2012
Dinesh
Dinesh

Reputation: 433

Calculate proper Time difference between minutes in SQL

I need to calculate the difference between the time from 15:00 to 16:00 in Minutes. If the callend exceeds 16:00:00 then i don't want to calculate it in between 15:00 to 16:00 and it should calculate in 16:00 to 17:00

I am using the below query to calculate in SQL

select SUM(datediff(second, callstart, callend))diff from tablex where    
CAST(callstart as TIME) BETWEEN '15:00:00' AND '16:00:00'

I am getting the results

diff
14

But I need the result 4. If the callstart and callend is in between 15:58 to 16:09 then I need to take the specific exceeded time to next column like diff16to17. For example i need to carry forward the time 9mins from 15:58 to 16:09 this to diff16to17 column

This is my table

    callstart                    callend
 2017-02-01 15:59:38.997    2017-02-01 16:09:39.833
 2017-02-01 15:56:47.720    2017-02-01 15:59:38.527
 2017-02-01 15:55:23.573    2017-02-01 15:56:47.207

Upvotes: 0

Views: 106

Answers (2)

Serg
Serg

Reputation: 22811

If i got it right

select sum(datediff(second,
                 case when CAST(callend as TIME) >='16:00:00'
                 then '16:00:00' else CAST(callstart as TIME) end
                 , CAST(callend as TIME)))  diff 

from (
    -- example data
    values
     ('2017-02-01 15:59:38.997','2017-02-01 16:09:39.833'),
     ('2017-02-01 15:56:47.720','2017-02-01 15:59:38.527'),
     ('2017-02-01 15:55:23.573','2017-02-01 15:56:47.207')
    )tablex(callstart, callend)

where    
CAST(callstart as TIME) BETWEEN '15:00:00' AND '16:00:00';

Upvotes: 1

Caleth
Caleth

Reputation: 63297

You just need to include the condition on callend in your WHERE

WHERE CAST(callstart as TIME) BETWEEN '15:00:00' AND '16:00:00'
  AND CAST(callend as TIME) < '16:00:00'

If this is a time-based billing calculation, you will need to figure out what the requirements are for the case where a call lasts over an hour, because as it is now they will not be included in any sum

To instead sum the minutes in the call that occur in an hour window, something like

WITH window AS
(    SELECT CASE WHEN CAST(callstart as TIME) < '15:00:00' THEN '15:00:00' ELSE callstart END as windowstart, 
            CASE WHEN CAST(callend as TIME) > '16:00:00' THEN '16:00:00' ELSE callend END as windowend
    FROM tablex
    WHERE CAST(callstart as TIME) < '16:00:00' AND CAST(callend as TIME) > '15:00:00'
)
SELECT SUM(datediff(second, windowstart, windowend)) as diff FROM window

Upvotes: 1

Related Questions