Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    17

    Unanswered: how to group the data to near 15 minutes (00,15,30,45) ?

    my table is like this :
    Code:
    stampin                      value
    1/4/2010 2:14:45 AM           1
    1/4/2010 2:14:46 AM            2
    1/4/2010 2:14:46 AM           1 
    1/4/2010 2:14:47 AM           2
    1/4/2010 2:14:49 AM           1
    1/4/2010 2:14:49 AM           1
    1/4/2010 2:14:50 AM           2
    1/4/2010 2:14:51 AM           2
    1/4/2010 2:14:52 AM           1
    1/4/2010 2:14:52 AM           1
    1/4/2010 2:14:53 AM           2
    this is my query : (group by date,hour and minutes only)

    SELECT CONVERT(VARCHAR(16),stampin,121) as fdate, count(*) from Table1 group by CONVERT(VARCHAR(16),stampin,121) order by fdate

    the result is :
    Code:
    stampin                 value
    1/4/2010 2:14:45            1
    1/4/2010 2:14:46            3
    1/4/2010 2:14:47            2
    1/4/2010 2:14:49            2
    1/4/2010 2:14:50            2
    1/4/2010 2:14:51            2
    1/4/2010 2:14:52            2
    1/4/2010 2:14:53            2

    but i wanna to grouping the data rounded to 15 minutes (00,15,30,45) ?
    my expected result is :
    Code:
    stampin                      value
    1/4/2010 2:14:45 AM           8
    1/4/2010 2:14:50 AM           8
    the group of data is rounded to 15 minutes, like this :
    00-14 become 00
    15-29 become 15
    30-44 become 30
    45-59 become 45

    Please help.
    thank's
    Jigu Haslim

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You should be able to accomplish this by using the DATEPART() function and integer division.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select dateadd(ss,datediff(ss, '20100101', stampin)/15*15,'20100101')
    ,sum(value)
    from Table1 
    group by datediff(ss, '20100101', stampin)/15*15

  4. #4
    Join Date
    Nov 2009
    Posts
    17
    thank's all
    this my query to solve the problem :
    Code:
    SELECT (CONVERT(VARCHAR(14),stampin,121)+RIGHT('00'+convert(varchar(2),floor(datepart(hh,stampin) / 15) * 15),2)) as fdate, count(*) 
    from Table1 
    group by (CONVERT(VARCHAR(14),stampin,121)+RIGHT('00'+convert(varchar(2),floor(datepart(hh,stampin) / 15) * 15),2)) 
    order by fdate

    thank's to pdreyer .. sorry i mean to minutes, not to second
    Quote Originally Posted by pdreyer View Post
    Code:
    select dateadd(ss,datediff(ss, '20100101', stampin)/15*15,'20100101')
    ,sum(value)
    from Table1 
    group by datediff(ss, '20100101', stampin)/15*15

    Code:
    select dateadd(n,datediff(n, '20100101', stampin)/15*15,'20100101')
    ,sum(value)
    from Table1 
    group by datediff(n, '20100101', stampin)/15*15


    which one is faster or efficient ??? my query or your query ??
    many thank's
    Jigu



    thank's
    Jigu
    Last edited by jigujigu2; 01-21-10 at 12:48.

Posting Permissions

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