Aggregate Select Results and convert Secs to Hours Mins.
-
I am wondering if someone can provide me with some of there awesome TSQL. i dont believe this script is too difficult and i will scale it down as i feel that i can modify the larger script if i can get assistance.
I need a script that will take the data listed below and i want to
PK | Date | AgentID | Email | TimeLoggedIn | CallDuration | Type
001 2023-02-23 1234 a@a.com 10:23:00.000 500 W
002 2023-02-23 2468 b@b.com 09:36:00.000 456 W
003 2023-02-22 1234 a@a.com 03:15:00.000 100 W
004 2023-02-23 1234 a@a.com 04:10:00.000 459 B
005 2023-02-23 2468 b@b.com 11:22:00.000 345 B
I would like to aggregate the select results from the table based on records with the matching dates grouped by Agent ID where Types are also the same I would also like to Sum the duration and change it to Hours Mins it is currently in seconds.
Any help would be next level.
-
Unless I'm missing something in the requirements, "awesome SQL" isn't required here... just a GROUP by on the columns you mention, a COUNT(), and a SUM() of the CallDuration column. Since this is for telephony, it is possible for the total of CallDuration to exceed 24 hours. Dividing the resulting sum by 3600 (integer math) will produce the number of hours and using modulus (%... not to be confused as "percent") 3600/60 will give you whole minutes.
Give it a try. As they say, "You can do this".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Create some test data:
DROP TABLE IF EXISTS #temp
GO
SELECT *
INTO #temp
FROM (VALUES
(001, CONVERT(DATE,'2023-02-23'), '123', 'a@a.com', '10:23:00.000', 500, 'W'),
(002, '2023-02-23', '246', 'b@b.com', '09:36:00.000', 456, 'W'),
(003, '2023-02-22', '123', 'a@a.com', '03:15:00.000', 100, 'W'),
(004, '2023-02-23', '123', 'a@a.com', '04:10:00.000', 459, 'B'),
(005, '2023-02-23', '246', 'b@b.com', '11:22:00.000', 345, 'B')) T(PK, Date, AgentID, Email, TimeLoggedIn, CallDuration, Type)
;Query
SELECT Date,
AgentID,
CONVERT(varchar(5), DATEADD(ss, SUM(CallDuration), 0), 114) CallDuration,
Type
FROM #temp
GROUP BY Date, AgentID, Type - Jonathan AC Roberts wrote:
Create some test data:
DROP TABLE IF EXISTS #temp
GO
SELECT *
INTO #temp
FROM (VALUES
(001, CONVERT(DATE,'2023-02-23'), '123', 'a@a.com', '10:23:00.000', 500, 'W'),
(002, '2023-02-23', '246', 'b@b.com', '09:36:00.000', 456, 'W'),
(003, '2023-02-22', '123', 'a@a.com', '03:15:00.000', 100, 'W'),
(004, '2023-02-23', '123', 'a@a.com', '04:10:00.000', 459, 'B'),
(005, '2023-02-23', '246', 'b@b.com', '11:22:00.000', 345, 'B')) T(PK, Date, AgentID, Email, TimeLoggedIn, CallDuration, Type)
;Query
SELECT Date,
AgentID,
CONVERT(varchar(5), DATEADD(ss, SUM(CallDuration), 0), 114) CallDuration,
Type
FROM #temp
GROUP BY Date, AgentID, TypeThat's nice but it's going to silently fail and produce the incorrect total call duration if it adds up to more than 23:59. You've also stolen the thunder of "If you want a coded answer, we need coded data" and getting the op to try something they probably already know. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@Wecks ...
Since these are CDRs, do you need to obey such rules as the classic "Whole minute rounding (up) per call" rule or any other some-such rules? Either way, which way do you want to round the seconds to get to minutes... Up, Down, or "natural"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-
natural will be fine no rounding up required. or rounding down.
-
Unless the "with the matching dates" is to be interpreted as a date range selection, in which case Jeff Moden's integer math would be needed to ensure that the 24 hour limit on time math isn't a factor.
In that case something like this might work:
SELECT
t.AgentID,
t.[TYPE],
CONCAT(CONVERT(VARCHAR(3), SUM(t.CallDuration)/3600),':',RIGHT('00' + CONVERT(VARCHAR(2),SUM(t.CallDuration)%3600/60),2)) AS AgrCallDurationHHMM
FROM #temp t
WHERE t.[DATE] BETWEEN '20230222' AND '20230223'
GROUP BY
t.AgentID,
t.[TYPE]; -
@Wecks ,
We still need an answer to my questions about the required rounding. Thanks.EDIT: Never mind... forgot to refresh before posting this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - kaj wrote:
Unless the "with the matching dates" is to be interpreted as a date range selection, in which case Jeff Moden's integer math would be needed to ensure that the 24 hour limit on time math isn't a factor.
For phone systems, you have to expect even a single call to violate the 24 hour duration. It happens a whole lot more than you might expect. Not so much with human "agents" but not all "agents" in a phone system are human. They'll frequently assign "Agent ID's" to devices that can maintain connection for a very long time.
A lot of folks will say "Well, 'It Depends" then, right?" or "Well, that'll NEVER happen"!
My answer is "NO". Always plan on it happening so that you don't get burned like I did in my early days (I cut my SQL teeth on phone systems) or like phone system manufacturers and their software developers don't plan on. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
That's nice but it's going to silently fail and produce the incorrect total call duration if it adds up to more than 23:59. You've also stolen the thunder of "If you want a coded answer, we need coded data" and getting the op to try something they probably already know. 🙂
Hopefully an agent will never be on the phone for more than 23:59 in any day.
I just pasted the data into SSMS and edited it using block select so it only took a couple of minutes to format it into a table, unlike some of the other questions that are asked.
Then after you wrote this I thought I'd see what ChatGPT could do to create a table with the data:
So I pasted the table in the question into ChatGPT requesting it makes a SQL Server table:
And voilà:
Here is the code you get by pressing the Copy Code button:
CREATE TABLE CallLogs (
PK int,
[Date] date,
AgentID int,
Email varchar(50),
TimeLoggedIn time,
CallDuration int,
Type char(1)
);
INSERT INTO CallLogs (PK, [Date], AgentID, Email, TimeLoggedIn, CallDuration, Type)
VALUES
(001, '2023-02-23', 1234, 'a@a.com', '10:23:00.000', 500, 'W'),
(002, '2023-02-23', 2468, 'b@b.com', '09:36:00.000', 456, 'W'),
(003, '2023-02-22', 1234, 'a@a.com', '03:15:00.000', 100, 'W'),
(004, '2023-02-23', 1234, 'a@a.com', '04:10:00.000', 459, 'B'),
(005, '2023-02-23', 2468, 'b@b.com', '11:22:00.000', 345, 'B'); - Jonathan AC Roberts wrote:
Then after you wrote this I thought I'd see what ChatGPT could do to create a table with the data:
So I pasted the table in the question into ChatGPT requesting it makes a SQL Server table:
Impressive but nice to see that it's certainly not ready to make us obsolete. It made two obvious mistakes. Can you see what they are?
"Must look eye" and "Trust but verify". 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Jonathan AC Roberts wrote:
Then after you wrote this I thought I'd see what ChatGPT could do to create a table with the data:
So I pasted the table in the question into ChatGPT requesting it makes a SQL Server table:
Impressive but nice to see that it's certainly not ready to make us obsolete. It made an obvious mistake. Can you see what it is?
"Must look eye" and "Trust but verify". 😀
I didn't notice anything wrong with it?
Except that Date is a valid name for a column without putting square brackets around it.
- Jonathan AC Roberts wrote:Jeff Moden wrote:Jonathan AC Roberts wrote:
Then after you wrote this I thought I'd see what ChatGPT could do to create a table with the data:
So I pasted the table in the question into ChatGPT requesting it makes a SQL Server table:
Impressive but nice to see that it's certainly not ready to make us obsolete. It made an obvious mistake. Can you see what it is?
"Must look eye" and "Trust but verify". 😀
I didn't notice anything wrong with it?
Except that Date is a valid name for a column without putting square brackets around it.
The OP explicitly included leading zeroes in the PK. Those are gone in the table because ChatGPT failed to recognize that as a string. It would have similar issues with zip codes and the like.
The column name "PK" is a strong indication that the column will be unique and also needs to be a constraint. In a lot of cases, that will also be the key for the Clustered Index and, in a phone system it would actually be a character based "Call ID". As Scott Pletcher would suggest, that's probably not the best use of the Clustered Index for this table but that's the way it's frequently designed in Phone Systems.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heh... where's "Clippy" when you need him? I can just see him saying "It looks like your trying to design a table to hold CDRs (Call Detail Records). Do you need some help with that"?
Of course, you should always turn off any cameras at night for either ChatGPT or when "Clippy" is around or...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply