Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
count events by 2 shift cycles using derived table
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 E911Data ([Agency] int, [EntryDateTime] datetime) ; INSERT INTO E911Data ([Agency], [EntryDateTime]) VALUES (1, '2015-01-01 22:05:44'), (1, '2015-01-01 22:05:50'), (1, '2015-01-01 22:06:02'), (1, '2015-01-01 22:06:13'), (1, '2015-01-02 00:10:17'), (1, '2015-01-02 02:55:17'), (1, '2015-01-02 02:55:22'), (1, '2015-01-02 02:55:34'), (1, '2015-01-02 02:55:45'), (1, '2015-01-02 03:50:41'), (1, '2015-01-02 03:50:50'), (1, '2015-01-02 03:50:57'), (1, '2015-01-02 03:51:12'), (1, '2015-01-02 05:27:49'), (1, '2015-01-02 05:27:57'), (1, '2015-01-02 05:28:05'), (1, '2015-01-02 05:28:19'), (1, '2015-01-05 12:09:45'), (1, '2015-01-05 12:09:56'), (1, '2015-01-05 12:26:16'), (1, '2015-01-05 12:26:25'), (1, '2015-01-05 12:26:36'), (1, '2015-01-05 12:26:47'), (1, '2015-01-05 12:28:22'), (1, '2015-01-05 12:45:00'), (1, '2015-01-05 12:45:08'), (1, '2015-01-05 12:45:19'), (1, '2015-01-05 12:45:30'), (1, '2015-01-05 17:29:56'), (1, '2015-01-05 17:30:07'), (1, '2015-01-05 17:30:15'), (1, '2015-01-05 17:30:29'), (1, '2015-01-05 17:49:08'), (1, '2015-01-05 17:49:15'), (1, '2015-01-05 17:49:23'), (1, '2015-01-05 17:49:37'), (1, '2015-01-05 18:01:14'), (1, '2015-01-05 18:01:24'), (1, '2016-01-01 22:05:44'), (1, '2016-01-01 22:05:50'), (1, '2016-01-01 22:06:02'), (1, '2016-01-01 22:06:13'), (1, '2016-01-02 00:10:17'), (1, '2016-01-02 02:55:17'), (1, '2016-01-02 02:55:22'), (1, '2016-01-02 02:55:34'), (1, '2016-01-02 02:55:45'), (1, '2016-01-02 03:50:41'), (1, '2016-01-02 03:50:50'), (1, '2016-01-02 03:50:57'), (1, '2016-01-02 03:51:12'), (1, '2016-01-02 05:27:49'), (1, '2016-01-02 05:27:57'), (1, '2016-01-02 05:28:05'), (1, '2016-01-02 05:28:19'), (1, '2016-01-05 16:09:45'), (1, '2016-01-05 16:09:56'), (1, '2016-01-05 16:26:16'), (1, '2016-01-05 16:26:25'), (1, '2016-01-05 16:26:36'), (1, '2016-01-05 16:26:47'), (1, '2016-01-05 16:28:22'), (1, '2016-01-05 16:45:00'), (1, '2016-01-05 16:45:08'), (1, '2016-01-05 16:45:19'), (1, '2016-01-05 16:45:30'), (1, '2016-01-05 17:29:56'), (1, '2016-01-05 17:30:07'), (1, '2016-01-05 17:30:15'), (1, '2016-01-05 17:30:29'), (1, '2016-01-05 17:49:08'), (1, '2016-01-05 17:49:15'), (1, '2016-01-05 17:49:23'), (1, '2016-01-05 17:49:37'), (1, '2016-01-05 18:01:14'), (1, '2016-01-05 18:01:24'), (1, '2016-01-05 18:01:32') ; declare @year int = 2016 SELECT CurYRshift_1_8hr , CurYRshift_2_8hr , CurYRshift_3_8hr , PreYRshift_1_8hr , PreYRshift_2_8hr , PreYRshift_3_8hr , CurYRshift_1_8hr - PreYRshift_1_8hr AS DiffShift_1_8hr , CurYRshift_2_8hr - PreYRshift_2_8hr AS DiffShift_2_8hr , CurYRshift_3_8hr - PreYRshift_3_8hr AS DiffShift_3_8hr , CurYRshift_1_12Hr , CurYRshift_2_12Hr , PreYRshift_1_12Hr , PreYRshift_2_12Hr , CurYRshift_1_12Hr - PreYRshift_1_12Hr AS DiffShift_1_12hr , CurYRshift_2_12Hr - PreYRshift_2_12Hr AS DiffShift_2_12hr FROM ( SELECT COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 1 THEN 1 END) AS CurYRshift_1_8hr , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 2 THEN 1 END) AS CurYRshift_2_8hr , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 3 THEN 1 END) AS CurYRshift_3_8hr , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 1 THEN 1 END) AS PreYRshift_1_8hr , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 2 THEN 1 END) AS PreYRshift_2_8hr , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 3 THEN 1 END) AS PreYRshift_3_8hr , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 1 THEN 1 END) AS CurYRshift_1_12Hr , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 2 THEN 1 END) AS CurYRshift_2_12Hr , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 1 THEN 1 END) AS PreYRshift_1_12Hr , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 2 THEN 1 END) AS PreYRshift_2_12Hr FROM ( select EntryYear , case when EntryHour < 7 or EntryHour > 23 then 3 when EntryHour >= 7 and EntryHour < 15 then 1 else 2 end hr8_shift_no , case when EntryHour >= 7 and EntryHour < 19 then 1 else 2 end hr12_shift_no from e911data cross apply (select datepart(hour,[EntryDateTime]) as EntryHour , datepart(year,[EntryDateTime]) - (@year-1) as EntryYear ) ca where EntryDateTime >= dateadd(year,(@year-1901),'19000101') /* 1 Jan year before @ year e.g. 2015 */ and EntryDateTime < dateadd(year,(@year-1899),'19000101') /* 1 Jan year after @ year e.g. 2017 */ ) AS derived ) AS d
View schema
Execution time: 0,02 sec, rows selected: 2, rows affected: 77, absolute service time: 0,19 sec
edit mode
|
history
|
discussion
CurYRshift_1_8hr
CurYRshift_2_8hr
CurYRshift_3_8hr
PreYRshift_1_8hr
PreYRshift_2_8hr
PreYRshift_3_8hr
DiffShift_1_8hr
DiffShift_2_8hr
DiffShift_3_8hr
CurYRshift_1_12Hr
CurYRshift_2_12Hr
PreYRshift_1_12Hr
PreYRshift_2_12Hr
DiffShift_1_12hr
DiffShift_2_12hr
1
0
26
13
11
14
13
-11
12
0
22
17
21
17
1
0