Second Query , Sql Server

Second Query

CREATE TABLE table1(DateTime DATETIME, Val INTEGER);

INSERT INTO table1 values('2013-01-02 01:02:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:04:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:06:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:08:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:10:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:12:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:14:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:16:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:18:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:20:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:22:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:24:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:26:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:28:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:30:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:32:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:34:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:36:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:38:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:40:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:42:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:44:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:46:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:48:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:50:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:52:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:54:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:56:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:58:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:00:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:02:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:04:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:06:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:08:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:10:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:12:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:14:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:16:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:18:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:20:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:22:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:24:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:26:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:28:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:30:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:32:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:34:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:36:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:38:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:40:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:42:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:44:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:46:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:48:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:50:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:52:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:54:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:56:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:58:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:00:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:02:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:04:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:06:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:08:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:10:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:12:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:14:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:16:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:18:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:20:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:22:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:24:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:26:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:28:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:30:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:32:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:34:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:36:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:38:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:40:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:42:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:44:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:46:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:48:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:50:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:52:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:54:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:56:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:58:00.000', 1);
INSERT INTO table1 values('2013-01-02 04:00:00.000', 1);

select 
case when datepart(mi,DateTime) < 5 
then dateadd(hh, datediff(hh, 0, DateTime)+0, 0)
when datepart(mi,DateTime) between 5 and 15
then dateadd(mi,5,dateadd(hh, datediff(hh, 0, DateTime)+0, 0))
when datepart(mi,DateTime) between 15 and 30
then dateadd(mi,15,dateadd(hh, datediff(hh, 0, DateTime)+0, 0))
else dateadd(mi,60,dateadd(hh, datediff(hh, 0, DateTime)+0, 0)) end as DateTime,count(*) as Val
from table1
group by case when datepart(mi,DateTime) < 5 
then dateadd(hh, datediff(hh, 0, DateTime)+0, 0)
when datepart(mi,DateTime) between 5 and 15
then dateadd(mi,5,dateadd(hh, datediff(hh, 0, DateTime)+0, 0))
when datepart(mi,DateTime) between 15 and 30
then dateadd(mi,15,dateadd(hh, datediff(hh, 0, DateTime)+0, 0))
else dateadd(mi,60,dateadd(hh, datediff(hh, 0, DateTime)+0, 0)) end


run \| edit \| history \| help	0