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.
| 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 |
|
|
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
|
|
|
|
|
|
|
Sum time over 24 hour period