How to write a JOIN statement to combine data from disparate tables
- by Amarundo
I have the following 2 procedures that I use as my source for a report. As of now, I'm presenting 2 different tables in my SQL Server Reporting Services 2008 R2 report, because it doesn't let me put them together as they belong to 2 different data sets.
I want to present them in a single table, but I have not been successful trying to use JOIN here. How do I do that?
NOTE: cName in IAgentQueueStats corresponds to UserId in AgentActivityLog.
/*** Aggregate values for Call Center Agents for calls, talk and hold time ***/
/*** The detail/row values is per 30-minute interval ***/
ALTER PROCEDURE [dbo].[sp_IAgentQueueStats_OnlyCalls_Grouped]
@p_StartDate datetime,
@p_EndDate datetime,
@p_Agents varchar(8000)
AS
SELECT [cName]
,sum([nAnswered]) SumNAnswered
,sum([nAnsweredAcd]) SumNAnsweredAcd
,sum([tTalkAcd]) SumTTalkAcd
,sum([nHoldAcd]) SumNHoldAcd
,sum([tHoldAcd]) SumTHoldAcd
,sum([tAcw]) SumTAcw
FROM [I3_IC].[dbo].[IAgentQueueStats]
WHERE dIntervalStart between @p_StartDate and DATEADD(s, 86400-1, @p_EndDate)
AND CHARINDEX ( cName ,@p_Agents)> 0
AND cReportGroup <> '*'
AND cHKey3 = '*' and cHKey4 ='*'
AND nEnteredAcd > 0
AND cReportGroup <> 'CCFax Email'
GROUP BY cName
And here is the second one:
/*** Aggregate values for Call Center Agents for status/activity time ***/
/*** The detail/row values is per start-time/end-time ***/
ALTER PROCEDURE [dbo].[sp_AgentActivity_Grouped]
@p_StartDate datetime,
@p_EndDate datetime,
@p_Agents varchar(8000)
AS
SELECT [UserId],[StatusCategory],SUM([StateDuration]) [StatusDuration] FROM
(
SELECT
[UserId]
,[StatusGroup]
,[StatusKey]
, CASE [StatusKey]
WHEN 'Available' THEN 'Productive'
WHEN 'Follow Up' THEN 'Productive'
WHEN 'Campaign Call' THEN 'Productive'
WHEN 'Awaiting Callback' THEN 'Productive'
WHEN 'In a Meeting' THEN 'Not Your Fault'
WHEN 'Project Work' THEN 'Not Your Fault'
WHEN 'At a Training Session'THEN 'Not Your Fault'
WHEN 'System Issues' THEN 'Not Your Fault'
WHEN 'Test' THEN 'Not Your Fault'
WHEN 'At Lunch' THEN 'Non Productive'
WHEN 'Available, Forward' THEN 'Non Productive'
WHEN 'Available, Follow-Me' THEN 'Non Productive'
WHEN 'At Play' THEN 'Non Productive'
WHEN 'AcdAgentNotAnswering' THEN 'Non Productive'
WHEN 'Do Not Disturb' THEN 'Non Productive'
WHEN 'Available, No ACD' THEN 'Non Productive'
WHEN 'Away from desk' THEN 'Non Productive'
ELSE [StatusKey]
END StatusCategory
,stateduration
FROM [I3_IC].[dbo].[AgentActivityLog]
WHERE [StatusDateTime] between @p_StartDate and DATEADD(s, 86400-1, @p_EndDate)
AND CHARINDEX ( [UserId] ,@p_Agents)> 0
AND [StatusKey] not in ('Gone Home','Out of the Office','On Vacation','Out of Town')
) a
GROUP BY [UserId],[StatusCategory]
ORDER BY [UserId], [StatusCategory] desc
BTW, if I take some time to comment/reply on your posts, it's not lack of interest, but of understanding...
