select ReferenceCode,
ContactType,
FirstName,
LastName,
Status,
Language,
Gender,
MAX(PrimaryPhone) AS PrimaryPhone,
MAX(HomePhone) AS HomePhone,
MAX(Workphone) AS Workphone,
MAX(MobilePhone) AS MobilePhone,
MAX(WorkPhoneAlt) AS WorkPhoneAlt,
MAX(MobilePhoneAlt) AS MobilePhoneAlt,
MAX(EmailAddress) AS EmailAddress,
MAX(EmailAddressAlt) AS EmailAddressAlt,
SMSPhone,
[Group1],
[Group2],
[Group3],
Institution
from
(
select 
s.sid as ReferenceCode, 
'Student' as ContactType, 
s.fname as FirstName,
s.lname as LastName,
'Class of ' + s.gradyear as Status,
isnull (l.description,'') as Language,
s.sex as Gender,

case when hi.homephone1 is NULL then ''
when hi.homephone1=' ' then ''
else '(' + substring (hi.homephone1, 1,3) + ') ' + substring (hi.homephone1, 5,8)
end as PrimaryPhone,
'' as HomePhone,
CASE WHEN cn.PhoneTitle like 'Father% Work Phone' THEN Number
END Workphone,
CASE WHEN cn.PhoneTitle like 'Father% Cell Phone' THEN Number
END MobilePhone,
'' as HomePhoneAlt,
CASE WHEN cn.PhoneTitle like 'Mother% Work Phone' THEN Number
END WorkPhoneAlt,
CASE WHEN cn.PhoneTitle like 'Mother% Cell Phone' THEN Number
END MobilePhoneAlt,
isnull (s.email,'') as EmailAddress,
'' as EmailAddressAlt,
'' as SMSPhone,
'' as [Group],
'' as [Group],
'' as [Group],
'001' as Institution
from student s
left join person p on p.personid=s.persid
left join codes l on l.code=s.prilang and l.grpid='langua'
left join studenthomeinfo hi on hi.persid=s.persid
left join StudentContactNumbers cn on cn.sno=s.sno
where s.status='A'
)t
GROUP BY ReferenceCode,
ContactType,
FirstName,
LastName,
Status,
Language,
Gender,
SMSPhone,
[Group1],
[Group2],
[Group3],
Institution
order by lname, fname

quote:

---

Originally posted by smantz

I have fixed most everything but am curious; What is the letter t for at the end of the inner query?
I know you have to have it to make it work, but I am not sure why?
Thanks again,
-SM

---