Calculate sum of duration SQL

Question

I use the following sql to calculate the duration in the first query.

CDate(TimeSerial(Val([EndTime])\100,Val([EndTime]) Mod 100,0)-TimeSerial(Val([StartTime])\100,Val([StartTime]) Mod 100,0))) AS Duration

And get the following which is good

And now, I would like to further calculate the total time of different lessons within a week in another query. Something like sum(iif(lesson="math",duration,0))

But since it's time data type sql server doesn't let me use the sum function.

I tried the following sql.

sum(hour(duration) + minute(duration)) AS Total_time

But it isn't what I expect because I would like to keep the original format with some criteria.

Is there a neat way to do so? Thank you.

What's in your time fields? You pretty much never want to Val a date or time field — Erik A
– Erik A, Commented Aug 5, 2020 at 13:42
It sounds like this question actually boils down to: How do I format a date with more than 24 hours as Hours:Days. Is that correct? — Erik A
– Erik A, Commented Aug 5, 2020 at 14:04
not really. The problem is that is there an easy way to sum over the duration in the format of hh:mm:ss — user13851309
– user13851309, Commented Aug 6, 2020 at 1:36

Gustav · Accepted Answer · 2020-08-06 07:17:14Z

1

First, just get the numeric duration:

TimeSerial(Val([EndTime])\100,Val([EndTime]) Mod 100,0)-TimeSerial(Val([StartTime])\100,Val([StartTime]) Mod 100,0) AS Duration

This you can sum and convert to DateTime:

CDate(Sum([duration])) AS Total_time

Example:

Data:

Queries:

SELECT 
    StartTime, 
    EndTime, 
    TimeSerial(Val([EndTime])\100,Val([EndTime]) Mod 100,0)-TimeSerial(Val([StartTime])\100,Val([StartTime]) Mod 100,0) AS Duration
FROM 
    tTest;

SELECT 
    Count(*) AS Slots, 
    CDate(Sum([Duration])) AS TotalHours
FROM 
    qTest;

Result:

edited Aug 6, 2020 at 7:17

answered Aug 5, 2020 at 14:00

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gustav Over a year ago

It certainly does. See extended answer, please.

Gordon Linoff · Accepted Answer · 2020-08-05 13:32:34Z

1

I would recommend just accepting the total time in seconds or decimal hours:

select datediff("s", starttime, endtime) as diff_seconds,
       datediff("s", starttime, endtime) / (60 * 60.0) as diff_hours

answered Aug 5, 2020 at 13:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

user13851309 Over a year ago

starttime and endtime are string

Gordon Linoff Over a year ago

@user13851309 . . . Convert them to date/times then for the datediff().

user13851309 Over a year ago

I change the question a bit which was confusing. I wanna calculate the total time in another query based on the duration.

Collectives™ on Stack Overflow

Calculate sum of duration SQL

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related