Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Post Unanswered: 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,

    Wheatster

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The 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

  3. #3
    Join Date
    Jul 2004
    Posts
    6

    Post Not an assignment...

    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Jul 2004
    Posts
    6

    24 hrs of data

    **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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select dateadd(minute, datediff(minute, @StartTime, @TestTime)/@IntervalMinutes, @StartTime) as Range,
    .
    .
    .
    from YourTable
    group by dateadd(minute, datediff(minute, @StartTime, @TestTime)/@IntervalMinutes, @StartTime)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I came up with:
    Code:
    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.theTable
    Blindman's query might work just as well, and it appears to be simpler.

    -PatP

  8. #8
    Join Date
    Jul 2004
    Posts
    6
    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

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    @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)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •