Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
count events by 2 shift cycles using CTE
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
run
|
edit
|
history
|
help
0
Find gaps in timesheet data between certain hours
jueves(08)
New
New_1
aaa
How to find 2nd highest salary
Common Table Expression
Common Table Expression
db
get first parent with a value