Sum time over 24 hour period - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sum time over 24 hour period

Author

Topic

dabram
Starting Member

14 Posts

Posted - 2010-08-20 : 10:39:31

How do I get the minutes for a 24 hour period. I need to get the total minutes from the EndDate - 24 hours.

create table TotalTime
(
ID int identity primary key,
StartDate datetime not null,
EndDate datetime not null
)

insert into TotalTime (StartDate, EndDate)

select '2010-06-11 03:11:00.000', '2010-06-11 09:10:00.000' union all
select '2009-09-18 06:20:00.000', '2009-09-18 14:17:00.000' union all
select '2010-08-05 21:15:00.000', '2010-08-05 23:30:00.000' union all
select '2010-06-13 18:13:00.000', '2010-06-14 02:03:00.000' union all
select '2010-06-19 19:44:00.000', '2010-06-20 05:10:00.000' union all
select '2010-06-20 06:19:00.000', '2010-06-20 12:20:00.000' union all
select '2010-06-20 18:19:00.000', '2010-06-21 02:34:00.000' union all
select '2010-06-21 04:45:00.000', '2010-06-21 10:05:00.000' union all
select '2010-06-21 12:15:00.000', '2010-06-21 20:23:00.000'

select *, DateDiff(MINUTE, StartDate, EndDate) as TotalMinutes
from Events

--Results
ID StartDate EndDate TotalMinutes
1 2010-06-11 03:11:00.000 2010-06-11 09:10:00.000 359
2 2009-09-18 06:20:00.000 2009-09-18 14:17:00.000 477
3 2010-08-05 21:15:00.000 2010-08-05 23:30:00.000 135
4 2010-06-13 18:13:00.000 2010-06-14 02:03:00.000 470
5 2010-06-19 19:44:00.000 2010-06-20 05:10:00.000 566
6 2010-06-20 06:19:00.000 2010-06-20 12:20:00.000 361
7 2010-06-20 18:19:00.000 2010-06-21 02:34:00.000 495
8 2010-06-21 04:45:00.000 2010-06-21 10:05:00.000 320
9 2010-06-21 12:15:00.000 2010-06-21 20:23:00.000 488

Best regards,

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-20 : 10:53:21

Can you also show us what your expected output should look like.

dabram
Starting Member

14 Posts

Posted - 2010-08-20 : 11:45:58

For simplicity, I just changed the dates and times. Last column showing the totals for a 24 hour period

ID StartDate EndDate TotalMinutes MinPast24Hours
1 2010-06-11 03:00:00.000 2010-06-11 09:00:00.000 360 360
2 2010-06-11 10:00:00.000 2010-06-11 12:00:00.000 120 480
3 2010-06-11 13:00:00.000 2010-06-11 15:00:00.000 120 600
4 2010-06-15 13:00:00.000 2010-06-15 15:00:00.000 120 120

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 12:02:54

how do you determine these intervals?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 12:04:02

this should give you a jump start i guess

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dabram
Starting Member

14 Posts

Posted - 2010-08-20 : 16:32:18

Thanks for the suggestion, I'll see what I come up with.

In answer to your question "how do you determine these intervals?"
The interval goes 24 hours back from the EndDate. If the Past24Hours start time is 10 minutes later than the start date, then the total time for the past 24 hours would be 25 minutes.

StartDate EndDate Past24Hours Minutes
2010-01-01 15:30 2010-01-01 16:00 2010-01-01 15:35 00:25
2010-01-02 13:35 2010-01-02 15:35 2010-01-01 15:35 02:00

Grand Total would then be 2:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources