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 table1GROUP 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