Another Multi-table join question

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

SQL Server 2005 Forums

Transact-SQL (2005)

Another Multi-table join question

Author

Topic

j2dna
Starting Member

11 Posts

Posted - 2010-02-12 : 11:39:55

I've tried multiple joins/unions. To best show what I'm trying to accomplish, I'll list it as a union. If I list Custom_Report.dbo.Get15Min(getdate()) as a group by clause I get the error "Each GROUP BY expression must contain at least one column that is not an outer reference."

Results I'm getting:
ESG Interval MaxQTime15 LongestTimeToAband15 CallsTo15 CallsQNow LongestQNow AgentsLoggedOn
CC_CLS_Consumer 2/12/2010 11:45 0 0 0 0 0 37
CC_CLS_Consumer 2/12/2010 11:45 38 3 49 0 0 0

Results wanted:
ESG Interval MaxQTime15 LongestTimeToAband15 CallsTo15 CallsQNow LongestQNow AgentsLoggedOn
CC_CLS_Consumer 2/12/2010 11:45 38 3 49 0 0 37

Query

select
esg = esg.EnterpriseName
, Interval = Custom_Report.dbo.Get15Min(getdate())
, MaxQTime15 = Custom_Report.dbo.GetDateString(0)
, LongestTimeToAband15 = Custom_Report.dbo.GetDateString(0)
, CallsTo15 = 0
, CallsQNow = sum(sgrt.CallsQueuedNow)
, LongestQNow = isnull(Custom_Report.dbo.GetDateString(datediff(ss, max(LongestCallQ), getdate())), 0)
, AgentsLoggedOn = sum(sgrt.LoggedOn)
from Skill_Group sg
, Enterprise_Skill_Group_Member esgm
, Enterprise_Skill_Group esg
, Skill_Group_Real_Time sgrt
where sg.SkillTargetID = esgm.SkillTargetID
and esgm.EnterpriseSkillGroupID = esg.EnterpriseSkillGroupID
and sg.SkillTargetID = sgrt.SkillTargetID
group by esg.EnterpriseName--, Custom_Report.dbo.Get15Min(getdate())

UNION

select
esg = esg.EnterpriseName
, Interval = Custom_Report.dbo.Get15Min(getdate())
, MaxQTime15 = isnull(Custom_Report.dbo.GetDateString(max(NetQTime + RingTime + LocalQTime)), 0)
, LongestTimeToAband15 = isnull(Custom_Report.dbo.GetDateString(max(TimeToAband)), 0)
, CallsTo15 = isnull(count(*), 0)
, CallsQNow = 0
, LongestQNow = 0
, AgentsLoggedOn = 0
from Termination_Call_Detail tcd
, Skill_Group sg
, Enterprise_Skill_Group_Member esgm
, Enterprise_Skill_Group esg
where tcd.DateTime >= dateadd(mi, -15, getdate())
and tcd.SkillGroupSkillTargetID = sg.SkillTargetID
and sg.SkillTargetID = esgm.SkillTargetID
and esgm.EnterpriseSkillGroupID = esg.EnterpriseSkillGroupID
group by esg.EnterpriseName--, Custom_Report.dbo.Get15Min(getdate())
order by esg.EnterpriseName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:45:29

[code]select esg,Interval,
sum(MaxQTime15),
sum(LongestTimeToAband15),
sum(CallsTo15),
... other fields
from
(
select
esg = esg.EnterpriseName
, Interval = Custom_Report.dbo.Get15Min(getdate())
, MaxQTime15 = Custom_Report.dbo.GetDateString(0)
, LongestTimeToAband15 = Custom_Report.dbo.GetDateString(0)
, CallsTo15 = 0
, CallsQNow = sum(sgrt.CallsQueuedNow)
, LongestQNow = isnull(Custom_Report.dbo.GetDateString(datediff(ss, max(LongestCallQ), getdate())), 0)
, AgentsLoggedOn = sum(sgrt.LoggedOn)
from Skill_Group sg
, Enterprise_Skill_Group_Member esgm
, Enterprise_Skill_Group esg
, Skill_Group_Real_Time sgrt
where sg.SkillTargetID = esgm.SkillTargetID
and esgm.EnterpriseSkillGroupID = esg.EnterpriseSkillGroupID
and sg.SkillTargetID = sgrt.SkillTargetID
group by esg.EnterpriseName--, Custom_Report.dbo.Get15Min(getdate())

UNION

select
esg = esg.EnterpriseName
, Interval = Custom_Report.dbo.Get15Min(getdate())
, MaxQTime15 = isnull(Custom_Report.dbo.GetDateString(max(NetQTime + RingTime + LocalQTime)), 0)
, LongestTimeToAband15 = isnull(Custom_Report.dbo.GetDateString(max(TimeToAband)), 0)
, CallsTo15 = isnull(count(*), 0)
, CallsQNow = 0
, LongestQNow = 0
, AgentsLoggedOn = 0
from Termination_Call_Detail tcd
, Skill_Group sg
, Enterprise_Skill_Group_Member esgm
, Enterprise_Skill_Group esg
where tcd.DateTime >= dateadd(mi, -15, getdate())
and tcd.SkillGroupSkillTargetID = sg.SkillTargetID
and sg.SkillTargetID = esgm.SkillTargetID
and esgm.EnterpriseSkillGroupID = esg.EnterpriseSkillGroupID
group by esg.EnterpriseName--, Custom_Report.dbo.Get15Min(getdate())

)t
group by esg,Interval
order by esg
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/