November 1, 2017 at 6:28 am
I need to generate a adhoc report. I need to query a time range(calltime) that is stored as char(5). Querying on only a one hour window works, but i need to select a two hour range.. THEN '14%' or '15%'
BUT i need a two hour time window not just the one hour like i have working.
Any suggestion?
Thank you
November 1, 2017 at 6:53 am
Chop the last three digits off of calltime so that you're left with just the hour portion, which you can convert to an integer and do proper arithmetic on. Please will you post a CREATE TABLE statement for CALLS, and some sample data in the form of INSERT statements?
John
November 1, 2017 at 7:00 am
CREATE TABLE [dbo].[CALLS](
[calldate] [DATETIME] NOT NULL,
[calltime] [CHAR](5) NOT NULL,
[duration] [CHAR](8) NOT NULL,
[duration_in_decimal] [DECIMAL](18, 7) NULL,
[ext] [CHAR](5) NOT NULL,
[trunk] [CHAR](5) NOT NULL,
[calltype] [CHAR](3) NULL,
[io] [CHAR](1) NOT NULL,
[areacode] [CHAR](3) NULL,
[phonenum] [CHAR](11) NULL,
[dept] [CHAR](20) NOT NULL,
[fname] [CHAR](15) NOT NULL,
[lname] [CHAR](20) NOT NULL,
[empnum] [NUMERIC](18, 0) NULL
) ON [PRIMARY]
Sample data
dept ext lname fname calls_total
NJSITRECC 4266 Rocco Nicholas 15
IRVITRECC 2861 Freeze Laura 13
TAMITRECC 2518 Streeter Michael 10
NJSITRECC 4238 Peters Jill 10
TAMITRECC 2517 Jean Jonathan 9
November 1, 2017 at 8:57 am
sample data in the form of INSERT statements
... and if you don't even include in your sample data the columns that are in your query, we really aren't going to be able to help!
John
November 1, 2017 at 10:38 am
Sorry...
INSERT [dbo].[CALLS] (calldate, calltime ,duration, duration_in_decimal, ext, trunk, calltype, io, areacode, phonenum, dept, fname, lname, empnum)
VALUES ('2017-10-31 00:00:00.000', '9:01','00:04:10', '0.0692000', '3301', '', 'TLD', 'O', '609', '6097601900', 'NJSITRECC', 'Brian', 'Merin', 23),
('2017-10-31 00:00:00.000', '10:01','00:04:10', '0.0692000', '0001', '', 'TLD', 'O', '609', '6097601900', 'TAMITRECC', 'Fname1', 'Lname1', 23),
('2017-10-31 00:00:00.000', '11:01','00:04:10', '0.0692000', '0002', '', 'TLD', 'O', '609', '6097601900', 'ATLITRECC', 'Fname2', 'Lname2', 23),
('2017-10-31 00:00:00.000', '12:01','00:04:10', '0.0692000', '0003', '', 'TLD', 'O', '609', '6097601900', 'SLVITRECC', 'Fname3', 'Lname3', 23),
('2017-10-31 00:00:00.000', '13:01','00:04:10', '0.0692000', '0004', '', 'TLD', 'O', '609', '6097601900', 'PHIITRECC', 'Fname4', 'Lname4', 23),
('2017-10-31 00:00:00.000', '14:01','00:04:10', '0.0692000', '0005', '', 'TLD', 'O', '609', '6097601900', 'PCCITRECC', 'Fname5', 'Lname5', 23),
('2017-10-31 00:00:00.000', '10:01','00:04:10', '0.0692000', '0005', '', 'TLD', 'O', '609', '6097601900', 'PCCITRECC', 'Fname6', 'Lname6', 23),
('2017-10-31 00:00:00.000', '15:01','00:04:10', '0.0692000', '0006', '', 'TLD', 'O', '609', '6097601900', 'CHIITRECC', 'Fname7', 'Lname7', 23),
('2017-10-31 00:00:00.000', '16:01','00:04:10', '0.0692000', '0007', '', 'TLD', 'O', '609', '6097601900', 'MINITRECC', 'Fname8', 'Lname8', 23),
('2017-10-31 00:00:00.000', '9:01','00:04:10', '0.0692000', '0008', '', 'TLD', 'O', '609', '6097601900', 'IRVITRECC', 'Fname9', 'Lname9', 23)
November 1, 2017 at 10:55 am
I don't have DDL or sample data for your dept table, so I can't test, but something like this:WITH TimesasInt AS (
SELECT
dept
, ext
, lname
, fname
, calldate
, CAST(LEFT('0'+calltime) AS tinyint) As TimeinHours
, dept
, time_zone
, io
, lname
, fname
, calls_total
FROM CALLS
)
SELECT dept, ext, lname, fname, COUNT(calldate) AS calls_total
FROM TimesasInt
WHERE TimeinHours +
CASE
WHEN dept IN (
SELECT dept FROM dept WHERE time_zone = 'est'
) THEN 1
WHEN dept IN (
SELECT dept FROM dept WHERE time_zone = 'cst')
) THEN 0
END IN (14,15)
AND io = 'O'
AND dept LIKE '%IT%'
AND calldate > '2017-10-19'
AND calldate < '2017-10-21'
GROUP BY dept , ext, lname, fname
ORDER BY calls_total DESC, dept DESC
I think there might be performance improvements to be had in the way you join to dept, as well.
John
November 1, 2017 at 1:32 pm
Thanks, but that didn't work as planned. I WAS ABLE to copy all data into another table and create a 'time' datatype. Now my issue changing the case statement to use : THEN between '09:01:00.0000000 ' and '11:01:00.0000000 '
SELECT dept, ext, lname, fname, COUNT(calldate) AS calls_total
FROM CALLS
WHERE (calltime LIKE
CASE
WHEN dept IN
(SELECT dept FROM dept WHERE time_zone = 'est')
THEN '14%'
WHEN dept IN
(SELECT dept FROM dept WHERE time_zone = 'cst')
THEN '13%'
END)
November 1, 2017 at 1:38 pm
If you are using SQL Server 2012, why aren't you using the TIME data type that has been available since SQL Server 2008?
November 1, 2017 at 2:45 pm
Lynn Pettis - Wednesday, November 1, 2017 1:38 PMIf you are using SQL Server 2012, why aren't you using the TIME data type that has been available since SQL Server 2008?
It's probably the fault of the phone system that produces the CDR (Call Detail Record) data. A lot of those had been written as early as SQL Server 7 and they've never updated the datatypes. The tables are probably from that phone system rather than anything the OP designed. Of course, only the OP knows for sure so I'll shut up now and let the OP answer the question. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2017 at 3:28 am
TryingToLearn - Wednesday, November 1, 2017 1:32 PMThanks, but that didn't work as planned. I WAS ABLE to copy all data into another table and create a 'time' datatype. Now my issue changing the case statement to use : THEN between '09:01:00.0000000 ' and '11:01:00.0000000 'SELECT dept, ext, lname, fname, COUNT(calldate) AS calls_total
FROM CALLS
WHERE (calltime LIKE
CASE
WHEN dept IN
(SELECT dept FROM dept WHERE time_zone = 'est')
THEN '14%'
WHEN dept IN
(SELECT dept FROM dept WHERE time_zone = 'cst')
THEN '13%'
END)
Where do 9:01 and 11:01 come from?! And how does the code you just posted differ from your original code? Don't forget we can't see your screen and we can't know your requirement if you don't explain it properly.. Please post DDL and sample data for the dept table, and the results you expect based on your sample data.
John
November 2, 2017 at 4:48 am
Those are time values stored in the time column, I've change that column to be type = time, I 'thought' now my problem was interrogating time differences in a case statement.
SELECT dept, ext, lname, fname, COUNT(calldate) AS calls_total
FROM CALLS
WHERE (calltime BETWEEN
CASE WHEN dept IN
(SELECT dept FROM dept
WHERE time_zone = 'est')
THEN '09:02:00.0000000' AND '11:02:00.0000000'
WHEN dept IN
(SELECT dept
FROM dept
WHERE time_zone = 'cst')
THEN '07:02:00.0000000' AND '09:02:00.0000000'
END)
AND (io = 'O')
AND (dept LIKE '%IT%') --RECC') OR (dept LIKE '%ITSALC')
AND (calldate > '2017-10-19')
-- AND (calldate < '2017-10-21')
GROUP BY dept , ext, lname, fname
ORDER BY calls_total DESC, dept DESC
November 2, 2017 at 5:10 am
...
WHERE calltime BETWEEN
CASE
WHEN dept IN (
SELECT dept FROM dept WHERE time_zone = 'est'
) THEN '09:02:00.0000000'
WHEN dept IN (
SELECT dept FROM dept WHERE time_zone = 'cst'
) THEN '07:02:00.0000000'
END
AND
CASE
WHEN dept IN (
SELECT dept FROM dept WHERE time_zone = 'est'
) THEN '11:02:00.0000000'
WHEN dept IN (
SELECT dept FROM dept WHERE time_zone = 'cst'
) THEN '09:02:00.0000000'
END
...
Like I said, post the details of the dept table for a tested solution that's more likely to have better performance.
John
November 2, 2017 at 5:22 am
Thank you so very much...Exactly what i needed...
November 2, 2017 at 7:20 am
TryingToLearn - Thursday, November 2, 2017 5:22 AMThank you so very much...Exactly what i needed...
SELECT
dept, ext, lname, fname, COUNT(calldate) AS calls_total
FROM CALLS c
CROSS APPLY (
SELECT TOP(1) -- TOP may not be necessary
StartTime = CASE WHEN d.time_zone = 'est' THEN '09:02:00.0000000' ELSE '07:02:00.0000000' END,
EndTime = CASE WHEN d.time_zone = 'est' THEN '11:02:00.0000000' ELSE '09:02:00.0000000' END
FROM dept d
WHERE d.dept = c.dept
) x
WHERE c.calltime BETWEEN StartTime AND EndTimeFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply