Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
count events by 2 shift cycles using CTE
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 ;with CTE as ( 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') ) select '8 hour' shift_type , shift_no , count(case when EntryYear = 0 then EntryYear end) prev_year , count(case when EntryYear = 1 then EntryYear end) this_year from (select 1 as shift_no union all select 2 union all select 3 ) s left join CTE on s.shift_no = CTE.hr8_shift_no group by s.shift_no union all select '12 hour' shift_type , shift_no , count(case when EntryYear = 0 then EntryYear end) prev_year , count(case when EntryYear = 1 then EntryYear end) this_year from (select 1 as shift_no union all select 2 ) s left join CTE on s.shift_no = CTE.hr12_shift_no group by s.shift_no
View schema
Execution time: 0,03 sec, rows selected: 6, rows affected: 77, absolute service time: 0,19 sec
edit mode
|
history
|
discussion
shift_type
shift_no
prev_year
this_year
1
8 hour
1
11
0
2
8 hour
2
14
26
3
8 hour
3
13
13
4
12 hour
1
21
22
5
12 hour
2
17
17
stackse - search stackoverflow differently