Aggregating rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Aggregating rows

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-10-11 : 09:44:44

Hi all

I've probably gone about this the wrong way, but here goes...

I've got a table which shows a breakdown of an agents telephony performance by 15 minute period. It shows what state the phone was in and the number of seconds for that state.
Now, if an agent has two states within a 15 minute period, I get two rows instead of one in the table (the headers run along the top).
I'm using the following to get the data:-

count(case activity.activityname when 'System_Default_Activity_Code' then ActivityTime end) as [System_Default_Activity_Code]

(there's 28 of those states in total)

Anyone know how I can get the data into one line per agent per 15 minute period without using a load of temp tables?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 10:17:09

It is always the same request...
Please give us table structure, sample data and wanted output in relation to the sample data.
That makes it a lot easier for us to help you.

No, you're never too old to Yak'n'Roll if you're too young to die.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-10-11 : 10:33:47

What I'm currently getting is one row per timestamp per activity. E.g.
[First Name] [Last Name] [Timestamp] [System Default Activity Code] [Reflection]
Fred Bloggs 07:00:00 460 0
Fred Bloggs 07:00:00 0 460

What I want to see is:
[First Name] [Last Name] [Timestamp] [System Default Activity Code] [Reflection]
Fred Bloggs 07:00:00 460 460

As I said above, there's 28 codes that could appear and I'm trying to get one record per timestamp per agent.

I can't give a sample of the raw data as it's held on the server as we don't have direct access.
(Apologies for the layout, but I couldn't figure out how to post a table.)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-11 : 10:59:46

Well -- what you do is you "make up" a temp table with the data in it. It's considered polite because otherwise we have to do work that you could have done easily yourself.

I'm guessing that this is "raw" data? What you've provided isn't the result of a SELECT statement. It's just data sitting in a table?

I could guess the datatypes but I'm not going to bother.

Please fill in this table and data





DECLARE @foo TABLE (
	[First Name] -- DATATYPE HERE
	, [Last Name] -- DATATYPE HERE
	, [TimeStamp] -- DATATYPE HERE (WHAT IS THIS is it A DATETIME, a CHAR....)
	, [System Default Activity Code] -- DATATYPE HERE
	, [Reflection] -- DATATYPE HERE
	)

INSERT @foo
      SELECT 'Fred', 'Bloggs', '07:00:00' ,460, 0
UNION SELECT 'Fred', 'Bloggs', '07:00:00' ,0  , 460

If this is actually the result of a SELECT statement you have then please post that statement.

I have *NO IDEA* what datatype your [timeStamp] is. is it a DATETIME, is it a string...... etc

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-10-11 : 11:42:26

OK, here's the results of the query.
FirstName Surname Timestamp System_Default_Activity_Code Wrap Time Wrap
###### ###### 01/10/2010 00:00:00 920 0 0
###### ###### 01/10/2010 00:00:00 0 92 0
###### ###### 01/10/2010 00:00:00 0 0 368

First name/Surname are char
Timestamp is Datetime
The rest are integers.

The select statement is as follows:-
use SymposiumDW


select
	agent.FirstName,
	agent.Surname,
	codes.Timestamp,
	count(case activity.activityname when 'System_Default_Activity_Code' then ActivityTime end) as [System_Default_Activity_Code],
	count(case activity.activityname when 'Wrap Time' then ActivityTime end) as [Wrap Time],
	count(case activity.activityname when 'Wrap' then ActivityTime end) as [Wrap],

from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
		join DIM_Activity on codes.HUBID=agent.HUBID

where
	DATEADD(DAY, 0, DATEDIFF(DAY, 0,codes.Timestamp)) >= '2010-10-01 00:00:00.000' 
	and DATEADD(DAY, 0, DATEDIFF(DAY, 0,codes.Timestamp)) < '2010-10-02 00:00:00.000' 
	and agent.VisibleCCCId = 3351

group by
	agent.FirstName,
	agent.Surname,
	codes.Timestamp,
	activity.ActivityName

order by
	codes.Timestamp

I've removed the name of the person in question and replaced it with #'s.

What I want to end up with is one row with all the relevant times on it per person.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-10-12 : 09:39:04

Anyone, please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources