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
 General SQL Server Forums
 New to SQL Server Programming
 How to group by date and time at 30 minute interva

Author  Topic 

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-04-02 : 19:04:33
I have a datetime column where I need to group by date and time at 30 minute interval.

Here is what I am working on.

select DATE, COUNT(*) from table1
GROUP BY DATE,(DATEPART(MINUTE, [DATE]) % 30)





CREATE TABLE TABLE1
(
[DATE] [datetime] NULL,
[STATUS] [int] NULL

) ON [PRIMARY]

INSERT INTO TABLE1
(DATE, STATUS)
VALUES
('2013-04-01 14:11:58.010', '1'),
('2013-04-01 14:18:58.027', '1'),
('2013-04-01 14:40:58.040', '1'),
('2013-04-01 14:50:58.027', '1'),
('2013-04-02 14:01:58.025', '1'),
('2013-04-02 14:14:58.027', '1'),
('2013-04-02 14:17:58.033', '1'),
('2013-04-02 14:31:58.027', '1')

Desired Result

Date Status
2013-04-01 14:30 3
2013-04-01 15:00 1
2013-04-02 14:00 3
2013-04-02 14:30 1



Any guidance or direction greatly appreciated.

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-02 : 20:49:09
I was going to say use something like shown below, but then that would not give you the results you want. How did the first 3 get grouped into time=14:30? I would have thought that if the first one at 14:11:58 got grouped into 14:30, then 14:40 should have gone to 15:00
select dateadd(mi,datediff(mi,0,[date])/30*30,0), COUNT(*) from table1
GROUP BY dateadd(mi,datediff(mi,0,[date])/30*30,0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-03 : 01:38:18
your posted output is wrong. how would 2013-04-01 14:30 have 3 as count? it should be 2 as there are only 2 records that happen in 14:30 slot
ie
('2013-04-01 14:11:58.010', '1'),
('2013-04-01 14:18:58.027', '1')


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-04-03 : 09:30:12
Thanks James/visakh16.
yes, provided output is incorrect.

Here is the corrected output:

Date Status
2013-04-01 14:00 1
2013-04-01 14:30 2
2013-04-01 15:00 1
2013-04-02 14:00 2
2013-04-02 14:30 2
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-03 : 11:30:24
[code]select dateadd(mi,(datediff(mi,0,[date])+15)/30*30,0), COUNT(*) from table1
GROUP BY dateadd(mi,(datediff(mi,0,[date])+15)/30*30,0)[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-03 : 13:47:08
quote:
Originally posted by emyk

Thanks James/visakh16.
yes, provided output is incorrect.

Here is the corrected output:

Date Status
2013-04-01 14:00 1
2013-04-01 14:30 2
2013-04-01 15:00 1
2013-04-02 14:00 2
2013-04-02 14:30 2



still not correct
explain how you got 1 for 14:00 as there's no value in sample data that happen before 14:00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-03 : 14:09:06
visakh i think he is rounding nearest half hour, and so the one row for 14:00 comes from the row with timestamp of '2013-04-01 14:11:58.010'
Go to Top of Page
   

- Advertisement -