Solved

Finding records between to times and spanning over midnight

Posted on 2016-11-20
21
43 Views
Last Modified: 2016-11-21
I have been unable to figure out how to select records between  1900 hrs to 0700 hrs that span over midnight   The result I get is Zero records returned.  Do I need to break this down into two select statements?   19 to 23 and 0 to 7 and add the results together or can this be done in one statement?  Below is a small portion of my sql statement from the stored procedure.  I have attached the full sp if needed.

The two select statements I have problems with are @Thirdshift... and@12HourSecond....

Using SQL Server 2014

DECLARE @ThirdShiftStart As Datetime
DECLARE @ThirdShiftEnd As Datetime

SET @ThirdShiftStart = 16  
SET @ThirdShiftEnd =   0    


Select

(SELECT Count(*) FROM E911Data WHERE Agency = @Agency and Year(EntryDateTime) = @Year and
      (datepart(hour,EntryDateTime) >= @ThirdShiftStart and datepart(hour,EntryDateTime) < @ThirdShiftEnd) as CurrentYearThirdShift
-TimeSpanningMidnight.txt
0
Comment
Question by:MikeM670
  • 9
  • 8
  • 4
21 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Use an AND condition for the first shift ie 07:00 to 19:00 (Exclusive)

SELECT   COUNT(*) AS TotalFirstShift
FROM     E911Data 
WHERE   Agency = @Agency 
AND        Year(EntryDateTime) = @Year 
AND        datepart(hour,EntryDateTime) >= @12HourFirstShiftStart   --- 07:00 AM
AND        datepart(hour,EntryDateTime) < @12HourFirstShiftEnd       --- 19:00 PM (Exclusive) 

Open in new window


.. and use OR for the second shift ie 19:00 to 07:00 AM (Exclusive)

SELECT   COUNT(*) AS TotalFirstShift
FROM     E911Data 
WHERE   Agency = @Agency 
AND        Year(EntryDateTime) = @Year 
AND       (	
                  datepart(hour,EntryDateTime) >= @12HourFirstShiftEnd  --- 19:00 PM
OR             datepart(hour,EntryDateTime) < @12HourFirstShiftStart   --- 07:00 AM (Exclusive)
               )

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> DECLARE @12HourFirstShiftStart As Datetime

Also, datePart returns an INTEGER. So the variable data type should be INT, not DATETIME.
0
 

Author Comment

by:MikeM670
Comment Utility
Hi _agx_

Thanks for getting back so fast and not replying sooner.    I'll plug the code in and see how it works for me.  Also the issue with the
>> DECLARE @12HourFirstShiftStart As Datetime was my fault as I had initially used an actual time reference and did not change the Dattime to int.
0
 

Author Comment

by:MikeM670
Comment Utility
It appears that the check for spanning midnight counts every record for the year not just those that occurred between 7pm and the following morning at 7am.  Should the code need a way to know we are asking for records for two different days but a continuous time range?

example...

From 1/1/2016 1900 Hrs through 0700 Hrs 1/2/2016



SELECT   COUNT(*) AS TotalSecondShift
FROM     E911Data
WHERE   Agency = @Agency
AND        Year(EntryDateTime) = @Year
AND       (      
               datepart(hour,EntryDateTime) >= @12HourSecondShiftEnd  --- 19:00 PM
OR             datepart(hour,EntryDateTime) < @12HourSecondShiftStart   --- 07:00 AM (Exclusive)
               )
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Have you considered use a CTE or temp table for that set of calculations?

one query could create a temp table for the wanted year of information, and calculate hour of start/end

and then using "conditional aggregates"?
i.e. using case expressions inside count you can aggregate different counts without having to re-scan the data every time

,  COUNT(case when shift_hrs = 8 then id end) as count_shift8
,  COUNT(case when shift_hrs = 12 then id end) as count_shift12
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I am making MANY assumptions about your table E911data.

For instance I assume that the column [EntryDateTime] is indexed and you really don't want to make your query slow by using a function on that column.  I'm also assuming there is more than just that column in the table, maybe one column for the start date/time and end date/ time (i.e. attendance).

With those assumptions, YOu might be able to use a CTE like this:
WITH E911CTE
AS (SELECT
                *
              , CASE
                        WHEN ShiftHours < 11 AND
                                EntryHour >= @FirstShiftStart AND
                                EntryHour < @FirstShiftEnd THEN 1
                        WHEN ShiftHours < 11 AND
                                EntryHour >= @SecondShiftStart AND
                                EntryHour < @SecondShiftEnd THEN 2
                        WHEN ShiftHours < 11 THEN 3
                        WHEN ShiftHours > 8 AND
                                EntryHour >= @12HourFirstShiftStart AND
                                EntryHour < @12HourSecondShiftEnd THEN 1
                        WHEN ShiftHours > 8 THEN 2
                END AS ShiftNumber
        FROM E911Data
        CROSS APPLY (SELECT
                        DATEDIFF(HOUR, StartShiftDateTime, EndShiftDateTime) AS ShiftHours
                      , DATEPART(HOUR, EntryDateTime)                        AS EntryHour
                      , DYEAR(EntryDateTime)                                 AS EntryYear) ca
        WHERE EntryDateTime > DATEADD(YEAR, (@year - 1899), '19000101'))
SELECT
        COUNT (CASE WHEN EntryYear = @year   and ShiftNumber = 1 AND ShiftHours < 11 THEN id) AS CurrentYearFirstShift
      , COUNT (CASE WHEN EntryYear = @year-1 and ShiftNumber = 1 AND ShiftHours < 11 THEN id) AS PreviousYearFirstShift
      , COUNT (CASE WHEN EntryYear = @year   and ShiftNumber = 2 AND ShiftHours < 11 THEN id) AS CurrentYearSecondShift
      , COUNT (CASE WHEN EntryYear = @year-1 and ShiftNumber = 2 AND ShiftHours < 11 THEN id) AS PreviousYearSecondShift
      , COUNT (CASE WHEN EntryYear = @year   and ShiftNumber = 3 AND ShiftHours < 11 THEN id) AS CurrentYearThirdShift
      , COUNT (CASE WHEN EntryYear = @year-1 and ShiftNumber = 3 AND ShiftHours < 11 THEN id) AS PreviousYearThirdShift
-- and so on
FROM E911CTE
;

Open in new window

or a simple "derived table" like this:
SELECT
    COUNT (CASE WHEN EntryYear = @year AND ShiftNumber = 1 AND ShiftHours < 11 THEN id) AS CurrentYearFirstShift
    , COUNT (CASE WHEN EntryYear = @year - 1 AND ShiftNumber = 1 AND ShiftHours < 11 THEN id) AS PreviousYearFirstShift
    , COUNT (CASE WHEN EntryYear = @year AND ShiftNumber = 2 AND ShiftHours < 11 THEN id) AS CurrentYearSecondShift
    , COUNT (CASE WHEN EntryYear = @year - 1 AND ShiftNumber = 2 AND ShiftHours < 11 THEN id) AS PreviousYearSecondShift
    , COUNT (CASE WHEN EntryYear = @year AND ShiftNumber = 3 AND ShiftHours < 11 THEN id) AS CurrentYearThirdShift
    , COUNT (CASE WHEN EntryYear = @year - 1 AND ShiftNumber = 3 AND ShiftHours < 11 THEN id) AS PreviousYearThirdShift
-- and so on
FROM (SELECT
                *
              , CASE
                        WHEN ShiftHours < 11 AND
                                EntryHour >= @FirstShiftStart AND
                                EntryHour < @FirstShiftEnd THEN 1
                        WHEN ShiftHours < 11 AND
                                EntryHour >= @SecondShiftStart AND
                                EntryHour < @SecondShiftEnd THEN 2
                        WHEN ShiftHours < 11 THEN 3
                        WHEN ShiftHours > 8 AND
                                EntryHour >= @12HourFirstShiftStart AND
                                EntryHour < @12HourSecondShiftEnd THEN 1
                        WHEN ShiftHours > 8 THEN 2
                END AS ShiftNumber
        FROM E911Data
        CROSS APPLY (SELECT
                        DATEDIFF(HOUR, StartShiftDateTime, EndShiftDateTime) AS ShiftHours
                      , DATEPART(HOUR, EntryDateTime)                        AS EntryHour
                      , DYEAR(EntryDateTime)                                 AS EntryYear) ca
        WHERE EntryDateTime > DATEADD(YEAR, (@year - 1899), '19000101')
    ) AS derived

Open in new window

note that the COUNT() function only incrments for non-null values so the case expressions above do not supply a value for else deliberately.

& (as already noted before) all you shift hour variable need to be integer
-- 8 Hour Shift Rotation

DECLARE @FirstShiftStart AS int
DECLARE @FirstShiftEnd AS int
DECLARE @SecondShiftStart AS int
DECLARE @SecondShiftEnd AS int
DECLARE @ThirdShiftStart AS int
DECLARE @ThirdShiftEnd AS int

-- 12 Hour Shift Rotation
DECLARE @12HourFirstShiftStart AS int
DECLARE @12HourFirstShiftEnd AS int
DECLARE @12HourSecondShiftStart AS int
DECLARE @12HourSecondShiftEnd AS int

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Should the code need a way to know we are asking for records for two different days but a continuous time range?

While I understand conceptually, I'm not sure I understand how two different days applies to the original queries - since they only check a single date/time value: EntryDateTime. Does the table actually contain a range, ie "StartTime" and "EndTime" that should be used instead of just the single EntryDateTime? Honestly, a Start/End range is more what I was expecting....  

Also, take not of Paul's comments above regarding query performance. Although datePart() works, it's not the best way to query an indexed column. Using functions will prevent the db from using the index effectively, ie making it slower.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"Honestly, a Start/End range is more what I was expecting"
me too
Most attendance recording involves both a start and an end

Could I ask that you share some sample rows from your SOURCE TABLES
(if there is anything private either exclude it or obfuscate it)
0
 

Author Comment

by:MikeM670
Comment Utility
Hi PortletPaul,

The EntryDateTime is the time the incoming 911 call is recorded in the database.  There is only a single time entry for when the call is received.  This database is for the Computer Aided Dispatch/Records Management program.  The actual 911 database would hold a more complete series of times related for the call.  I've included a cut and past of a few different sections of the EntryDateTime field for reference.

As for performance I am attempting to write some stored procedures for use with Crystal Reports in preparing a Annual Report.  So I'm not sure if I really need optimal performance but I guess that never hurts.  

Agency      EntryDateTime
----------- -----------------------
1           2016-01-01 22:05:44.000
1           2016-01-01 22:05:50.000
1           2016-01-01 22:06:02.000
1           2016-01-01 22:06:13.000
1           2016-01-02 00:10:17.000
1           2016-01-02 02:55:17.000
1           2016-01-02 02:55:22.000
1           2016-01-02 02:55:34.000
1           2016-01-02 02:55:45.000
1           2016-01-02 03:50:41.000
1           2016-01-02 03:50:50.000
1           2016-01-02 03:50:57.000
1           2016-01-02 03:51:12.000
1           2016-01-02 05:27:49.000
1           2016-01-02 05:27:57.000
1           2016-01-02 05:28:05.000
1           2016-01-02 05:28:19.000
1           2016-01-05 16:09:45.000
1           2016-01-05 16:09:56.000
1           2016-01-05 16:26:16.000
1           2016-01-05 16:26:25.000
1           2016-01-05 16:26:36.000
1           2016-01-05 16:26:47.000
1           2016-01-05 16:28:22.000
1           2016-01-05 16:45:00.000
1           2016-01-05 16:45:08.000
1           2016-01-05 16:45:19.000
1           2016-01-05 16:45:30.000
1           2016-01-05 17:29:56.000
1           2016-01-05 17:30:07.000
1           2016-01-05 17:30:15.000
1           2016-01-05 17:30:29.000
1           2016-01-05 17:49:08.000
1           2016-01-05 17:49:15.000
1           2016-01-05 17:49:23.000
1           2016-01-05 17:49:37.000
1           2016-01-05 18:01:14.000
1           2016-01-05 18:01:24.000
1           2016-01-05 18:01:32.000


_agx_

While I understand conceptually, I'm not sure I understand how two different days applies to the original queries - since they only check a single date/time value: EntryDateTime. Does the table actually contain a range, ie "StartTime" and "EndTime" that should be used instead of just the single EntryDateTime? Honestly, a Start/End range is more what I was expecting....

Maybe I was not clear on what I needed to accomplish.

The EntryDateTime field contains an entry for each time a 911 call is received.  So the time the call was received is placed into the table field EntryDateTime just once along with any other incoming data received from the actual 911 system.  
I want to find all EntryDateTime entries that fall within the shifts.  The SP should return counts for each year based on shifts for the current year , previous year and the count difference either positive or negative for them.


Here is some example data that lets say represents all the calls received for that particular time range for the entire year.
Shift:   0700 to 1900  would contain the following.  

2016-06-20 07:21:54.000
2016-06-20 08:46:09.000
2016-06-20 10:41:00.000
2016-06-20 18:55:44.000

So the count for this shift would be 4

Shift 1900 to 0700   (This shift spans overnight into the next day.)
2016-06-20 19:10:15.000
2016-06-20 22:00:07.000
2016-06-20 23:38:05.000
2016-06-21 00:02:01.000
2016-06-21 05:49:50.000
2016-06-21 06:55:47.000

So the count for this shift would be 6

I would also like this to work for a '8 hour' three shift report..

>= 0700 and < 1500  (First Shift)
>=1500 and < 2300   (Second Shift)
>= 2300 and < 0700  (Third Shift)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Great. Thanks.

I'd like to verify something: a single 911 will therefore be in both an 8 hour shift and a 12 hour shift.

Is that correct?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:MikeM670
Comment Utility
Hi PortletPaul,

Yes it can appear in either the 8 hour shift or a 12 hour shift.  Think of it this way.  A 911 center may operate as three shifts of 8 hours or two shifts of 12.  So what I'm looking to count is how man 911 calls are received in each shift period.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Please note that the cross apply is used merely so that I can refer to the alias [EntryHour] inside the select clause, it juts makes it easier to read.

declare @year int = 2016

select
      EntryHour
    , EntryDateTime
    , 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 ) ca
where EntryDateTime > dateadd(year,(@year-1901),'19000101')
order by
      EntryHour
    , EntryDateTime
    

Open in new window

+----+-----------+---------------------+--------------+---------------+
|    | EntryHour |    EntryDateTime    | hr8_shift_no | hr12_shift_no |
+----+-----------+---------------------+--------------+---------------+
|  1 |         0 | 02.01.2016 00:10:17 |            3 |             2 |
|  2 |         2 | 02.01.2016 02:55:17 |            3 |             2 |
|  3 |         2 | 02.01.2016 02:55:22 |            3 |             2 |
|  4 |         2 | 02.01.2016 02:55:34 |            3 |             2 |
|  5 |         2 | 02.01.2016 02:55:45 |            3 |             2 |
|  6 |         3 | 02.01.2016 03:50:41 |            3 |             2 |
|  7 |         3 | 02.01.2016 03:50:50 |            3 |             2 |
|  8 |         3 | 02.01.2016 03:50:57 |            3 |             2 |
|  9 |         3 | 02.01.2016 03:51:12 |            3 |             2 |
| 10 |         5 | 02.01.2016 05:27:49 |            3 |             2 |
| 11 |         5 | 02.01.2016 05:27:57 |            3 |             2 |
| 12 |         5 | 02.01.2016 05:28:05 |            3 |             2 |
| 13 |         5 | 02.01.2016 05:28:19 |            3 |             2 |
| 14 |        16 | 05.01.2016 16:09:45 |            2 |             1 |
| 15 |        16 | 05.01.2016 16:09:56 |            2 |             1 |
| 16 |        16 | 05.01.2016 16:26:16 |            2 |             1 |
| 17 |        16 | 05.01.2016 16:26:25 |            2 |             1 |
| 18 |        16 | 05.01.2016 16:26:36 |            2 |             1 |
| 19 |        16 | 05.01.2016 16:26:47 |            2 |             1 |
| 20 |        16 | 05.01.2016 16:28:22 |            2 |             1 |
| 21 |        16 | 05.01.2016 16:45:00 |            2 |             1 |
| 22 |        16 | 05.01.2016 16:45:08 |            2 |             1 |
| 23 |        16 | 05.01.2016 16:45:19 |            2 |             1 |
| 24 |        16 | 05.01.2016 16:45:30 |            2 |             1 |
| 25 |        17 | 05.01.2016 17:29:56 |            2 |             1 |
| 26 |        17 | 05.01.2016 17:30:07 |            2 |             1 |
| 27 |        17 | 05.01.2016 17:30:15 |            2 |             1 |
| 28 |        17 | 05.01.2016 17:30:29 |            2 |             1 |
| 29 |        17 | 05.01.2016 17:49:08 |            2 |             1 |
| 30 |        17 | 05.01.2016 17:49:15 |            2 |             1 |
| 31 |        17 | 05.01.2016 17:49:23 |            2 |             1 |
| 32 |        17 | 05.01.2016 17:49:37 |            2 |             1 |
| 33 |        18 | 05.01.2016 18:01:14 |            2 |             1 |
| 34 |        18 | 05.01.2016 18:01:24 |            2 |             1 |
| 35 |        18 | 05.01.2016 18:01:32 |            2 |             1 |
| 36 |        22 | 01.01.2016 22:05:44 |            2 |             2 |
| 37 |        22 | 01.01.2016 22:05:50 |            2 |             2 |
| 38 |        22 | 01.01.2016 22:06:02 |            2 |             2 |
| 39 |        22 | 01.01.2016 22:06:13 |            2 |             2 |
+----+-----------+---------------------+--------------+---------------+

Open in new window

CREATE TABLE E911Data
    ([Agency] int, [EntryDateTime] datetime)
;
    
INSERT INTO E911Data
    ([Agency], [EntryDateTime])
VALUES
    (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')
;

Open in new window

0
 

Author Comment

by:MikeM670
Comment Utility
Hi PortletPaul,

I will confess that I don't fully understand some of the code you have and it does return the records properly!  I can see applications for this right now but its not quit what I'm looking for as a end result.  So can we take this a bit further?

As I mentioned before I'm using Crystal Reports to build this annual report.  What I have done in the past is use what they call a command to execute a stored procedure and retrieve records from the db which allows me to select the values returned from the command and place them into my report.  So what I need is for this to be in a stored procedure that has the Agency and Year as the two parameters which the current code has in place.  The sp should return data counts in separate columns in this fashion.

For example:

Current Year  8hr Shift1      Previous Year  8r  Shift1     DiffCntBetween 8hr Shift 1    .... continued below
           100                                             80                                          20
...

Current Year  8hr Shift2     Previous Year  8r  Shift2     DiffCntBetween 8hr Shift 2    .... continued below
           60                                             120                                          -60

Current Year  8hr Shift3     Previous Year  8r  Shift3    DiffCntBetween 8hr Shift 3    .... continued below
           60                                             60                                          0

TotalCallsCurrentYear (ALL Shifts)           TotalCallsPreviousYear (ALL Shifts)            DiffCntBetweenYears
            xxxx                                                                     xxxx                                                                  xxxxx
The same would hold true for the 12 hour shifts.
Also a total call count for the Year period would round out what is needed.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
This result:
+---+------------+----------+-----------+-----------+
|   | 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 |
+---+------------+----------+-----------+-----------+

Open in new window

Produced by this query:
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

Open in new window

Using this sample data:
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')
;

Open in new window

also see: http://rextester.com/discussion/TFF44340/count-events-by-2-shift-cycles-using-CTE
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 150 total points
Comment Utility
where EntryDateTime > dateadd(year,(@year-1901),'19000101')

Looks good. One small suggestion. Set an upper limit on the date range. Otherwise, the results could potentially include multiple years.  Example, if @year = 2016:

     WHERE  EntryDateTime >= dateadd(year,(@year-1900),'19000101')      -- '2016-01-01' 
     AND      EntryDateTime < dateadd(year,(@year-1900)+1,'19000101')     -- '2017-01-01'

Open in new window


... or use a separate @startDate variable in the real proc for clarity, ie ...

      
      ....
      DECLARE @StartDate DATE
      SET @StartDate = dateadd(year, (@year-1900), '19000101')
      .... etc...
      WHERE  EntryDateTime >= @startDate 
      AND      EntryDateTime < dateadd(year, 1, @startDate)

Open in new window

0
 

Author Comment

by:MikeM670
Comment Utility
In this instance using Crystal Reports I need to results returned in a very specified way so the data will display properly.
The results of the Query should


|   | CurYRshift_1_8hr | CurYRshift_2_8hr | CurYRshift_3_8hr | PreYRshift_n1_8hr | PreYRshift_n2_8hr | PreYRshift_n3_8hr | DiffShift_1_8hr | .....other Shift Times.... 
+---+------------------+------------------+------------------+-------------------+-------------------+-------------------+---------
| 1 |                0 |               26 |               13 |                11 |                14 |                13 |   (the different between cur & pre years)
(Only the single row of data counts returned above)

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
Comment Utility
So use case expressions inside counts in a single query. It's not that dissimilar to what you started with, just that you decide on the year and shifts in one derived table, then count in the next query "layer" up. I added an upper boundary to the date range  (which is a good idea), the range should provide for a full 2 years of data no matter which year you ask for.
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

Open in new window


+---+------------------+------------------+------------------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+-------------------+-------------------+-------------------+------------------+------------------+
|   | 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 |
+---+------------------+------------------+------------------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+-------------------+-------------------+-------------------+------------------+------------------+

Open in new window

see: http://rextester.com/discussion/UJPP34504/count-events-by-2-shift-cycles-using-derived-table
0
 

Author Comment

by:MikeM670
Comment Utility
That does look like exactly what I need.  I did add a check for what agency the e911data is for in the where clause.  There might be more then one agency in the database that will answer 911 calls.  

 where EntryDateTime >= dateadd(year,(@year-1901),'19000101') /* 1 Jan year before @ year e.g. 2015 */
              and EntryDateTime <  dateadd(year,(@year-1899),'19000101') and Agency = @Agency /* 1 Jan year after @ year  e.g. 2017 */ 
          ) AS derived

Open in new window




declare @Agency = 1
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') and Agency = @Agency /* 1 Jan year after @ year  e.g. 2017 */ 
          ) AS derived
    ) AS d

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
That's fine, add more conditions as required, although I would have added a new row so the comment remained relevant to that row.

Don't know if you need to consider using integer variables inside the derived table to represent the shift breaks?

                , 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

but that is only necessary IF those shift changeover points do alter at all. If not then constants are ok I presume.
0
 

Author Comment

by:MikeM670
Comment Utility
I think it would be a good idea to use integer variables.   In my career I have worked some shifts that were non standard.   I.e. 8 am to 4 pm instead of the normal 7 am to 3 pm.
0
 

Author Closing Comment

by:MikeM670
Comment Utility
I want to thank both of you for helping me with finding records that span over midnight using various work shift configurations.  PortletPaul you went above and beyond normal help!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now