Group data by intervals - SQL Server Forums

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);

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources