MikeM670
asked on
Finding records between to times and spanning over midnight
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,EntryDateTi
-TimeSpanningMidnight.txt
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,EntryDateTi
-TimeSpanningMidnight.txt
>> DECLARE @12HourFirstShiftStart As Datetime
Also, datePart returns an INTEGER. So the variable data type should be INT, not DATETIME.
Also, datePart returns an INTEGER. So the variable data type should be INT, not DATETIME.
ASKER
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.
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.
ASKER
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,EntryDateTim
OR datepart(hour,EntryDateTim
)
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,EntryDateTim
OR datepart(hour,EntryDateTim
)
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
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
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:
& (as already noted before) all you shift hour variable need to be integer
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
;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& (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 intShould 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.
>>"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)
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)
ASKER
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_
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)
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)
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?
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?
ASKER
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.
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.
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
+----+-----------+---------------------+--------------+---------------+
| | 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 |
+----+-----------+---------------------+--------------+---------------+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')
;ASKER
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.
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.
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 |
+---+------------+----------+-----------+-----------+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_noCREATE 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')
;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 deriveddeclare @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
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.
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.
ASKER
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.
ASKER
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!!
Open in new window
.. and use OR for the second shift ie 19:00 to 07:00 AM (Exclusive)
Open in new window