count events by 2 shift cycles using derived table, Sql Server

count events by 2 shift cycles using derived table

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


run \| edit \| history \| help	0