grouping in ranges

Table:

SomeFKID <pk>
StartDateTime <pk>
EndDateTime
SomeValue

Example Scenario:

Data is stored hourly. So there would be 24 records for today for each SomeFKID. I need to be able to pass a TimeSpan (in minutes), a StartDateTime, and an EndDateTime to a stored procedure and return totals in the date range grouped by the TimeSpan. So if I want all records today grouped by 2 hour intervals I would need to pass:
7/1/2004 00:00:00, 7/1/2004 23:59:59, 120 --> and return 12 records one for hours 0-2, one for hours 2-4, etc.

Any advice would be greatly appreciated!

Thanks in advance,

WheatsterThe assignment had to have given you more detail than that. Does the teacher want splits pro-rated? Do you select based solely on start time? What do you aggregate?

I think this would be a lot easier if you just posted the whole assignment and let us look at it.

-PatP|||Based soley on start time is fine. Value Column aggregated. If I am missing something really obvious then please just inform me of where I need to look in the tsql help files.

Also, this will work for the prototype I am working on now, but in the future the requested Split may be smaller than the span. In other words 15 minutes where data is stored hourly...but I can deal with that later.|||I'm still unsure of how to help you get to your goal. Can you at least post 24 hours (rows) worth of sample data, with the result sets that you'd like to see for 60, 90, and 120 minutes?

-PatP|||**For 60 I want the view below
**For 120 I want rows 1&2 grouped, 3&4 grouped, etc.
**Lets just use 60 minute increments for now. 90 can be dealt with later

ScheduleID StartDateTime EndDateTime Value
---- ---------------- ---------------- ----------------
1 2004-04-10 07:00:00.000 2004-04-10 08:00:00.000 257.0
1 2004-04-10 08:00:00.000 2004-04-10 09:00:00.000 252.0
1 2004-04-10 09:00:00.000 2004-04-10 10:00:00.000 242.0
1 2004-04-10 10:00:00.000 2004-04-10 11:00:00.000 247.0
1 2004-04-10 11:00:00.000 2004-04-10 12:00:00.000 257.0
1 2004-04-10 12:00:00.000 2004-04-10 13:00:00.000 282.0
1 2004-04-10 13:00:00.000 2004-04-10 14:00:00.000 317.0
1 2004-04-10 14:00:00.000 2004-04-10 15:00:00.000 347.0
1 2004-04-10 15:00:00.000 2004-04-10 16:00:00.000 352.0
1 2004-04-10 16:00:00.000 2004-04-10 17:00:00.000 342.0
1 2004-04-10 17:00:00.000 2004-04-10 18:00:00.000 317.0
1 2004-04-10 18:00:00.000 2004-04-10 19:00:00.000 302.0
1 2004-04-10 19:00:00.000 2004-04-10 20:00:00.000 287.0
1 2004-04-10 20:00:00.000 2004-04-10 21:00:00.000 277.0
1 2004-04-10 21:00:00.000 2004-04-10 22:00:00.000 267.0
1 2004-04-10 22:00:00.000 2004-04-10 23:00:00.000 262.0
1 2004-04-10 23:00:00.000 2004-04-11 00:00:00.000 267.0
1 2004-04-11 00:00:00.000 2004-04-11 01:00:00.000 277.0
1 2004-04-11 01:00:00.000 2004-04-11 02:00:00.000 287.0
1 2004-04-11 02:00:00.000 2004-04-11 03:00:00.000 297.0
1 2004-04-11 03:00:00.000 2004-04-11 04:00:00.000 307.0
1 2004-04-11 04:00:00.000 2004-04-11 05:00:00.000 297.0
1 2004-04-11 05:00:00.000 2004-04-11 06:00:00.000 277.0
1 2004-04-11 06:00:00.000 2004-04-11 07:00:00.000 252.0
1 2004-12-12 00:00:00.000 2004-12-12 01:00:00.000 12.0|||select dateadd(minute, datediff(minute, @.StartTime, @.TestTime)/@.IntervalMinutes, @.StartTime) as Range,
.
.
.
from YourTable
group by dateadd(minute, datediff(minute, @.StartTime, @.TestTime)/@.IntervalMinutes, @.StartTime)|||I came up with:CREATE TABLE dbo.theTable (
ScheduleID INT
, StartDateTime DATETIME
, EndDateTime DATETIME
, Value DECIMAL (4, 1)
)

INSERT INTO theTable (ScheduleId, StartDateTime, EndDateTime, Value)
SELECT 1, '2004-04-10 07:00:00.000', '2004-04-10 08:00:00.000', 257.0
UNION SELECT 1, '2004-04-10 08:00:00.000', '2004-04-10 09:00:00.000', 252.0
UNION SELECT 1, '2004-04-10 09:00:00.000', '2004-04-10 10:00:00.000', 242.0
UNION SELECT 1, '2004-04-10 10:00:00.000', '2004-04-10 11:00:00.000', 247.0
UNION SELECT 1, '2004-04-10 11:00:00.000', '2004-04-10 12:00:00.000', 257.0
UNION SELECT 1, '2004-04-10 12:00:00.000', '2004-04-10 13:00:00.000', 282.0
UNION SELECT 1, '2004-04-10 13:00:00.000', '2004-04-10 14:00:00.000', 317.0
UNION SELECT 1, '2004-04-10 14:00:00.000', '2004-04-10 15:00:00.000', 347.0
UNION SELECT 1, '2004-04-10 15:00:00.000', '2004-04-10 16:00:00.000', 352.0
UNION SELECT 1, '2004-04-10 16:00:00.000', '2004-04-10 17:00:00.000', 342.0
UNION SELECT 1, '2004-04-10 17:00:00.000', '2004-04-10 18:00:00.000', 317.0
UNION SELECT 1, '2004-04-10 18:00:00.000', '2004-04-10 19:00:00.000', 302.0
UNION SELECT 1, '2004-04-10 19:00:00.000', '2004-04-10 20:00:00.000', 287.0
UNION SELECT 1, '2004-04-10 20:00:00.000', '2004-04-10 21:00:00.000', 277.0
UNION SELECT 1, '2004-04-10 21:00:00.000', '2004-04-10 22:00:00.000', 267.0
UNION SELECT 1, '2004-04-10 22:00:00.000', '2004-04-10 23:00:00.000', 262.0
UNION SELECT 1, '2004-04-10 23:00:00.000', '2004-04-11 00:00:00.000', 267.0
UNION SELECT 1, '2004-04-11 00:00:00.000', '2004-04-11 01:00:00.000', 277.0
UNION SELECT 1, '2004-04-11 01:00:00.000', '2004-04-11 02:00:00.000', 287.0
UNION SELECT 1, '2004-04-11 02:00:00.000', '2004-04-11 03:00:00.000', 297.0
UNION SELECT 1, '2004-04-11 03:00:00.000', '2004-04-11 04:00:00.000', 307.0
UNION SELECT 1, '2004-04-11 04:00:00.000', '2004-04-11 05:00:00.000', 297.0
UNION SELECT 1, '2004-04-11 05:00:00.000', '2004-04-11 06:00:00.000', 277.0
UNION SELECT 1, '2004-04-11 06:00:00.000', '2004-04-11 07:00:00.000', 252.0
UNION SELECT 1, '2004-12-12 00:00:00.000', '2004-12-12 01:00:00.000', 12.0
GO
-- ptp 20040701 re: http://www.dbforums.com/t1003286.html

CREATE PROCEDURE dbo.theQuery
@.pdStart DATETIME
, @.pdEnd DATETIME
, @.piInterval INT
AS

IF 0 <> @.piInterval % 60 RETURN

SELECT DateAdd(minute, delta * @.piInterval, @.pdStart), Sum(Value)
FROM (SELECT DateDiff(minute, @.pdStart, StartDateTime)
/ @.piInterval AS delta, Value
FROM dbo.theTable
WHERE StartDateTime BETWEEN @.pdStart AND @.pdEnd) AS a
GROUP BY delta

RETURN
GO

EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 60
EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 90
EXECUTE dbo.theQuery '2004-04-10 12:00', '2004-04-11 12:00', 120

DROP PROCEDURE dbo.theQuery
DROP TABLE dbo.theTableBlindman's query might work just as well, and it appears to be simpler.

-PatP|||Thanks guys, I really appreciate this! Pat, I just tested and your solution definitely works fine.

Blindman I tested but and not sure what you meant by @.TestDate, I tried a few scenarios and none worked.

Wheatster|||@.TestDate is the value you are checking to see what group range it belongs in.

select dateadd(minute, datediff(minute, @.StartTime, [YourDateTimeValue])/@.IntervalMinutes, @.StartTime) as Range,
.
.
.
from YourTable
group by dateadd(minute, datediff(minute, @.StartTime, [YourDateTimeValue])/@.IntervalMinutes, @.StartTime)