Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Rextester implements OUTPUT feature of Update ~ Delete (2016 >)
Task2_Final
transaction in sql server
Creating tables in sql server
Emp Table
db
store2
Students
archu
repert