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.

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Group data by intervals

Author  Topic 

ertweety
Starting Member

15 Posts

Posted - 2014-03-26 : 13:10:07

Here is the code I have so far;
select to_char(o.dt,'hh24:mi') as hr_dy, count (o.membership_num) as tl
from [table] o
where o.dt >= '2014-03-25' and o.dt <'2014-03-26'
GROUP BY to_char(o.dt,'hh24:mi')


E.g. of times are 9:24, 9:36, etc.
I want to do a count of memberships by 30 minute intervals between the hours of 9am and 6pm. Help. Don't know how to count the 9:24 time in the first 30 minute interval and 9:36 in the second.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-26 : 13:40:42
What database system are you using? This forum is for Microsoft SQL Server, so expertise on Oracle, MySQL etc. is rare. If you were using Microsoft SQL Server, this is how I would write it. I don't think this would work in other RDBMS because of the DATEADD and DATEDIFF functions not being available on those.
SELECT  DATEADD(mi,DATEDIFF(mi,0,o.dt)/30*30,0) AS hr_dy ,
COUNT(o.membership_num) AS tl
FROM [table] o
WHERE o.dt >= '20140325'
AND o.dt < '20140326'
GROUP BY DATEADD(mi,DATEDIFF(mi,0,o.dt)/30*30,0);
Go to Top of Page

ertweety
Starting Member

15 Posts

Posted - 2014-03-26 : 14:19:37
Sorry, I am using Oracle, pl/sql. Maybe I wrote in the wrong forum.
Go to Top of Page
   

- Advertisement -