Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Grouping by 15 min intervals

    Hi,

    I have a table called tbl_site_visits:

    id int primary key autoincrement
    site Varchar
    visit_date datetime


    how can do a count of the number of visits between a certain date and group them by site and 15 min interval between the hours of
    8.00am and 5.00pm

    so the results would be as follows:

    Code:
                        0800  0815  0830 0845 ....
    site A               2     3       0     1
    site B               1     3       0     2

    I can get them grouped by visit_date but it skips intervals where there are no visits i.e. 0830


    I've been playing around with a number_table and doing a left join but cant seem to get this working

    Code:
    select tme = dateadd(minute, n * 15, '2011-06-27')
    
    from  number_table
    
    where n betwen 0 and 95

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Arrow

    This will get you your intervals:
    Code:
    select	id,
    	site,
    	DATEADD(minute, datediff(minute, 0, GETDATE())/15*15, 0) as Interval
    from	tbl_site_visits
    Not sure how you want it pivoted or grouped, but you should really be doing that in your reporting tool anyway, and not in sql.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    i need it grouped by site and visit_date. since visit_date is a timestamp I only want to group by time at 15 min intervals.

    so if the user inputs parameters dates e.g between 2011-06-27 and 2011-06-28
    i need the query to count all visits between 8.00am and 5.00pm grouped in 15 min intervals.

    e.g. sample data:

    Code:
    1, site A, 2011-06-27 08:05:00
    2, site A, 2011-06-27 08:10:12
    3, site A, 2011-06-27 08:31:22
    4, site A, 2011-06-28 08:07:00
    This should give me:

    Code:
                     0800   0815  0830  0845 ....
    site A            3       0      1
    Even if i group on site and visit_date using your method above it will skip out showing interval 0815 since no visits took place between 0815 and 0830.
    I need it to display all 15 min interval between 8.00am and 5.00pm regardless of there being any visits between that time and the next interval.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you must pivot in SQL, then extend this to include your other time buckets:

    Code:
    with TestData as
    	(select	1 as ID, 'site A' as Site, '2011-06-27 08:05:00' as Visit_Date
    	union select	2 as ID, 'site A' as Site, '2011-06-27 08:10:12' as Visit_Date
    	union select	3 as ID, 'site A' as Site, '2011-06-27 08:31:22' as Visit_Date
    	union select	4 as ID, 'site A' as Site, '2011-06-28 08:07:00' as Visit_Date)
    select	*
    into	#site_visits
    from	TestData
    
    select	Site,
    	DATEADD(DAY, DATEDIFF(day, 0, Visit_Date), 0) as Visit_Date,
    	sum(case when DATEPART(HOUR, Visit_Date) = 8 and (DATEPART(minute, Visit_Date)/15) = 0 then 1 else 0 end) as '0800',
    	sum(case when DATEPART(HOUR, Visit_Date) = 8 and (DATEPART(minute, Visit_Date)/15) = 1 then 1 else 0 end) as '0815',
    	sum(case when DATEPART(HOUR, Visit_Date) = 8 and (DATEPART(minute, Visit_Date)/15) = 2 then 1 else 0 end) as '0830',
    	sum(case when DATEPART(HOUR, Visit_Date) = 8 and (DATEPART(minute, Visit_Date)/15) = 3 then 1 else 0 end) as '0845'
    from	#site_visits
    group by Site,
    	DATEADD(DAY, DATEDIFF(day, 0, Visit_Date), 0)
    
    drop table #site_visits
    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
  •