Hey everyone!
I´m having really big difficulties with merging two types of SQL scripts into one.
Been working for it on hours but I just can´t get it right. Can anyone put me in the right correction and save my day?
Thank you so much in advance!
My script I want to add more figures to:
[php]select
CONVERT(VARCHAR(10), t4.DateTime, 120) as Date,
–t2.AgentTeamID, t3.SkillTargetID,
t2.EnterpriseName as Agent_Team_Name,
t3.EnterpriseName as Agent_Name, count(t4.WrapupData) as ‘Handled’ ,
–CAST(DATEPART(HOUR, t4.DateTime) AS char(2)) + ‘:00’ as ‘By Hour’,
–RIGHT(CONVERT(CHAR(8),DATEADD(second,sum (t4.TalkTime),0),108),5) as TalkTime,
sum (t4.TalkTime) as TalkTime,
sum (t4.TalkTime + t4.HoldTime + WorkTime) as AHT,
t4.WrapupData from dbo.t_Agent_Team_Member t1
inner join t_Agent_Team t2 on t2.AgentTeamID = t1.AgentTeamID
inner join t_Agent t3 on t1.SkillTargetID = t3.SkillTargetID
inner join stpeu_hds.dbo.t_Termination_Call_Detail t4 on t4.AgentSkillTargetID = t1.SkillTargetID
where t4.DbDateTime between :start_date and :stop_date and WrapupData in (:WrapUp) and WrapupData is not null and t2.AgentTeamID in (:Team_Name)
group by
CONVERT(VARCHAR(10), t4.DateTime, 120) ,
t3.EnterpriseName
–, DATEPART(HOUR, t4.DateTime) ,
,t4.WrapupData
,t2.AgentTeamID,t3.SkillTargetID, t2.EnterpriseName
order by Date,Agent_Team_Name,Agent_Name, WrapupData[/php]
My script I want to merge with the script above:
[php]SELECT AgentTeamName= AgentSkillGroup.AgentTeamName ,
Interval = AgentSkillGroup.Interval,
FullName = AgentSkillGroup.FullName,
AgentSkillID= AgentSkillGroup.AgentSkillID,
AgentTeamSkillID= AgentSkillGroup.AgentTeamSkillID,
CallsHandled = AgentSkillGroup.CallsHandled,
TalkTime= AgentSkillGroup.TalkTime,
LoggedOnTime= AgentHalfHour.LoggedOnTime,
AHT = ISNULL(AgentSkillGroup.HandledCallsTime / AgentSkillGroup.CallsHandled, 0),
perAktivTid = ISNULL(AgentSkillGroup.TalkTime + AgentSkillGroup.HoldTime * 1.0 / AgentHalfHour.LoggedOnTime, 0),
AktivTid = ISNULL(AgentSkillGroup.TalkTime + AgentSkillGroup.HoldTime * 1.0, 0)
FROM (SELECT
Agent_Team.EnterpriseName AS AgentTeamName,
Media_Routing_Domain.EnterpriseName AS Media, ASGHH.DateTime AS Interval,
CONVERT(char(10), ASGHH.DateTime, 101) AS Date,
Person.LastName + ', ’ + Person.FirstName AS FullName,
Agent.SkillTargetID AS AgentSkillID,
Agent_Team.AgentTeamID AS AgentTeamSkillID,
SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,
sum(isnull(ASGHH.TalkPreviewTime,0)) + sum(isnull(ASGHH.TalkReserveTime,0))AS TalkTime,
SUM(ISNULL(ASGHH.HandledCallsTime, 0)) AS HandledCallsTime,
SUM(ISNULL(ASGHH.LoggedOnTime, 0)) AS LoggedOnTime,
SUM(ISNULL(ASGHH.HoldTime, 0)) AS HoldTime,
SUM(ISNULL(ASGHH.AvailTime, 0)) AS AvailTime,
SUM(ISNULL(ASGHH.NotReadyTime, 0)) AS NotReadyTime,
SUM(ISNULL(ASGHH.ReservedStateTime, 0)) AS ReservedTime,
SUM(ISNULL(ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime, 0)) AS WrapTime,
MAX(ASGHH.DbDateTime) AS DbDateTime
FROM
Agent (nolock),
–Agent_Skill_Group_Half_Hour ASGHH (nolock),
Agent_Skill_Group_Interval ASGHH (nolock),
Agent_Team_Member (nolock),
Agent_Team (nolock),
Skill_Group (nolock),
Person (nolock),
Media_Routing_Domain (nolock)
WHERE
( ASGHH.SkillGroupSkillTargetID = Skill_Group.SkillTargetID) and
( Media_Routing_Domain.MRDomainID = Skill_Group.MRDomainID) and
( Skill_Group.SkillTargetID NOT IN (SELECT BaseSkillTargetID FROM Skill_Group (nolock) WHERE (Priority > 0) AND (Deleted <> ‘Y’)))
and
( Agent.SkillTargetID = ASGHH.SkillTargetID ) and
( Agent.SkillTargetID = Agent_Team_Member.SkillTargetID ) and
( Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID ) and
( Agent.PersonID = Person.PersonID )
GROUP BY
Agent_Team.AgentTeamID,
Agent_Team.EnterpriseName,
Agent_Team.PriSupervisorSkillTargetID,
Agent.SkillTargetID,
Person.LastName,
Person.FirstName,
Media_Routing_Domain.EnterpriseName, ASGHH.DateTime) AgentSkillGroup,
(SELECT
Media = Media_Routing_Domain.EnterpriseName,
LoggedOnTime =sum( AHH.LoggedOnTime),
AvailTime = SUM(ISNULL(AHH.AvailTime, 0)),
NotReadyTime = SUM(ISNULL(AHH.NotReadyTime, 0)),
AgentSkillID = AHH.SkillTargetID,
Interval=AHH.DateTime FROM Agent (nolock), Agent_Interval AHH (nolock), Media_Routing_Domain (nolock) WHERE Agent.SkillTargetID= AHH.SkillTargetID and
AHH.MRDomainID = Media_Routing_Domain.MRDomainID
GROUP BY AHH.SkillTargetID,Media_Routing_Domain.EnterpriseName,AHH.DateTime) AgentHalfHour
Where AgentSkillGroup.AgentSkillID = AgentHalfHour.AgentSkillID and AgentSkillGroup.Interval = AgentHalfHour.Interval and AgentSkillGroup.Media = AgentHalfHour.Media[/php]
