Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Second Query
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
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
View schema
Execution time: 0,03 sec, rows selected: 10, rows affected: 90, absolute service time: 0,19 sec
edit mode
|
history
|
discussion
DateTime
Val
1
02.01.2013 01:00:00
2
2
02.01.2013 01:05:00
5
3
02.01.2013 01:15:00
8
4
02.01.2013 02:00:00
17
5
02.01.2013 02:05:00
5
6
02.01.2013 02:15:00
8
7
02.01.2013 03:00:00
17
8
02.01.2013 03:05:00
5
9
02.01.2013 03:15:00
8
10
02.01.2013 04:00:00
15