|
masterdineen
Aged Yak Warrior
United Kingdom
522 Posts |
 Posted - 07/20/2012 : 09:24:11
|
Hello there. I have the following pivot script below.
it looks fine to me but im getting an error regarding a column name but cannot see where iam going wrong.
Select LOOKUP_FULL_DESC, [Affiliate], [Associate],[Member], [Certified],[Fellow]
from
( select lookup_Class.LOOKUP_FULL_DESC as 'class', (mem.member_ref)
individual ind WITH (NOLOCK) , member mem,
attribute attribute1 WITH (NOLOCK) ,
membership_history memHist WITH (NOLOCK) , lookup lookup,
lookup lookup_class,
Membership_grade grade,
membership_plan memPlan WITH (NOLOCK) , member mem2 WITH (NOLOCK)
where mem.join_date < (SELECT DATEADD(month,datediff(month,-0,getdate())-0,+1))
and mem.individual_ref = ind.individual_ref
and mem.member_class = lookup_class.lookup_ref
and UPPER(attribute1.attribute_code) =
(select UPPER((SELECT convert(varchar(3),datename(month,DATEADD(month,datediff(month,-0,getdate())+2,-0))))))
and attribute1.individual_ref = ind.individual_ref
and mem.leave_date is null
and memPlan.member_plan_ref = memHist.member_plan_ref
and memHist.valid_from = (SELECT DATEADD(month,datediff(month,-0,getdate())+2,-0))
and memHist.HISTORY_STATUS = lookup.lookup_ref
--and grade.MEMBER_GRADE_REF = mem2.MEMBER_GRADE
and (memhist.member_ref = mem2.member_ref
or memHist.feepaying_member = mem2.member_ref)
and mem2.individual_ref = ind.individual_ref
--group by Lookup.LOOKUP_FULL_DESC
) AS sources
pivot
(
count(member_ref) for class
in ([Affiliate], [Associate],[Member], [Certified],[Fellow] )
)
as piv
----------------------------------------------------------------
error
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LOOKUP_FULL_DESC'.
|
|
Pivot error... puzzling
