Querying time values spanning 2 days
-
Hi all.
I'm trying to develop a query for grouping help desk calls based on time of day received with the following groupings: 5:00 PM to 2:00 AM, 9:00 PM to 6:00 AM, 1:00 AM to 10:00 AM, and 9:00 AM to 6:00 PM. The date and time data is stored in the DB in a decimal format, so I'm having to convert these values to datetime, which was very challenging to begin with. Problem is when the time periods span 2 days I get no data back. I've tried including the date with the time and still no results. Any ideas on how I can resolve this?
Thanks!
TC
-
1. Please, Can you post some sample Data and DDL
2. If you include the date(with no time) in the grouping you should be fine
something like:
select date, timeSpan
from SourceOfData
group by date, Timespan
should return what you need. Without sample data I can't go further
* Noel -
Note that with a 24 hour clock, to determine if a specific time is with a range that spans midnight, such as 9:00 PM to 6:00 AM, two checks are needed: one for 9PM to Midnight (24) and the other for Midnight (00) to 6AM.
Try something along the lines of this:
Create table HelpDeskCalls
(CallIdinteger not null primary key
, CallTsdatetime not null
)
select count(*) as TotalCalls
,sum(CASE when DATEPART(hh,CallTs) between 17 and 24 OR DATEPART(hh,CallTs) between 00 and 02
then 1 else null end ) as [5:00 PM to 2:00 AM]
,sum(CASE when DATEPART(hh,CallTs) between 21 and 24 OR DATEPART(hh,CallTs) between 00 and 06
then 1 else null end ) as [9:00 PM to 6:00 AM]
,sum(CASE when DATEPART(hh,CallTs) between 01 and 10
then 1 else null end ) as [1:00 AM to 10:00 AM]
,sum(CASE when DATEPART(hh,CallTs) between 09 and 18
then 1 else null end ) as [9:00 AM to 6:00 PM]
From HelpDeskCalls
go
SQL = Scarcely Qualifies as a Language
-
Actually I already determined that. My only concern is when grouping by day of week, as I think the data returned would actually only be for that specific date as opposed to spanning say 9:00 PM to Midnight Thursday and Midnight to 6:00 AM Friday, but since I'm reporting on a weekly basis I guess this isn't an issue. Here's a sample of the code I wrote:
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
SELECT DISTINCT CALL_HDW.CALLID_HDW AS CALLS_RCVD,
CALL_HDW.CALLGRP_HDW AS GRPID_HDW,
CALL_HDW.CALLOPR_HDW AS OPERID_HDW,
LEFT(CONVERT(DATETIME,CAST(((CONVERT(NCHAR,CALL_HDW.CALLDAT_HDW) -
((CONVERT(NCHAR,CALL_HDW.CALLDAT_HDW)/65536) * 65536))/256)
AS NCHAR(2))+'/'+
CAST(CONVERT(NCHAR,CALL_HDW.CALLDAT_HDW)-
((CONVERT(NCHAR,CALL_HDW.CALLDAT_HDW)/65536)*65536)-
((((CONVERT(NCHAR,CALL_HDW.CALLDAT_HDW) -
((CONVERT(NCHAR,CALL_HDW.CALLDAT_HDW)/65536) * 65536))/256))*256)
AS NCHAR(2))+'/'+
CAST(CONVERT(NCHAR,CALL_HDW.CALLDAT_HDW)/65536 AS NCHAR(4))),12)+
RIGHT(CONVERT(DATETIME,CAST(CAST(CONVERT(DATETIME,(CALL_HDW.CALLTME_HDW/16777216))
AS NUMERIC(2)) AS NCHAR(2)) +':'+
CAST(CAST(((CALL_HDW.CALLTME_HDW-
(CAST((CALL_HDW.CALLTME_HDW/16777216) AS NUMERIC(2))
*16777216))/65536) AS NUMERIC(2))AS NCHAR(2))),8) AS CALLTME_HDW
INTO #TIME
FROM CALL_HDW
SELECT GRPID_HDW,
OPERID_HDW,
CALLTME_HDW,
CASE WHEN DATEPART(HH,CALLTME_HDW)
BETWEEN 17
AND 24
OR DATEPART(HH,CALLTME_HDW) BETWEEN 00 AND 02
THEN CAST('5PM_to_2AM' AS VARCHAR(21))
WHEN DATEPART(HH,CALLTME_HDW)
BETWEEN 21
AND 24
OR DATEPART(HH,CALLTME_HDW) BETWEEN 00 AND 06
THEN CAST('9PM_to_6AM' AS VARCHAR(21))
WHEN DATEPART(HH,CALLTME_HDW)
BETWEEN 01
AND 10
THEN CAST('1AM_to_10AM' AS VARCHAR(21))
WHEN DATEPART(HH,CALLTME_HDW)
BETWEEN 09
AND 18
THEN CAST('9AM_to_6PM'AS VARCHAR(21))
ELSE CAST('WHATEVER' AS VARCHAR(21)) END AS CALLTME_HDW
FROM #TIME
DROP TABLE #TIME
The date and time data in this DB is in a decimal format, hence the protracted statements to convert to datetime.
Thanks,
Tim C.
-
All your problems stem from using the wrong unit of measure (day). Radically simplify the solution by defining a "relative" work week for each shift. Create a table with the following four columns:
1. ShiftCode
2. SpanNbr
3. SpanStartMinsOffset
4. SpanStopMinsOffset
All offsets are from say Sunday 12am. You can quickly calculate all time spans for the week for each shift no matter which day boundaries are crossed.
Convert to "absolute" work week with something like this:
Select DateAdd(minute,@Sun, SpanStartMinsOffset) as AbsShiftStart
-
What do you mean "decimal format"?
Is it number of days from any "zero day"?
If yes, there is no any problem.
1. Don't convert data in column you are filtering. SQL Server cannot predict result of the function, it must read whole table, convert every value, store it in tempdb, than scan this temporary table for requested values, join selected rows to original table, and than select requested rows.
No surprise it's gonna take 2 days for big enough table.
2. Set up index on the date-time column (I mean not datatype, but meaning of data). Because you select periods of data this index must be clustered.
3. Convert requested periods in your query to the same type as you have time periods stored in your table.
For example, for weekly periods:
WHERE CallTime%7 between convert(float, @TimeStart) and convert(float, @TimeEnd)
@TimeStart = convert(datetime, '1900-01-03 09:00)
@TimeEnd = convert(datetime, '1900-01-03 17:00)
This will return all calls on 3rd day of the week started from your "zero date" between 9am and 5pm.
P.S. Don't convert datetime to char type. Never. Except it's your main enemy's database.
_____________
Code for TallyGenerator -
I hope that post on relative work span offsets made sense. Create a view on your original table to handle the decimal to datetime conversions and hide that complexity forever. Hope this helps.
-
And even better create computed columns
CALLTME_HDW%7 (for weekly periods) and
CALLTME_HDW%1(for dayly periods)
and set up indexes on it.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply