SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to group by date and time at 30 minute interva
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emyk
Yak Posting Veteran

57 Posts

Posted - 04/02/2013 :  19:04:33  Show Profile  Reply with Quote
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

Edited by - emyk on 04/02/2013 19:36:13

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/02/2013 :  20:49:09  Show Profile  Reply with Quote
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)

Edited by - James K on 04/02/2013 20:49:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/03/2013 :  01:38:18  Show Profile  Reply with Quote
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 - 04/03/2013 :  09:30:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/03/2013 :  11:30:24  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/03/2013 :  13:47:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/03/2013 :  14:09:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000