+ ' ' + RTRIM(UPPER(ISNULL(dbo.Street.Street_Name, '')))
+ ' ' + RTRIM(UPPER((COALESCE(CASE WHEN dbo.Street.Suffix_Cnty IS NOT NULL THEN Suffix_Full
END, ''))))) AS Name
FROM dbo.Segment INNER JOIN dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID
Order By Name
When Running this Query i get close to the right results but i need to tweak something. I'm stuck at this part.
Suffix_Bell is the abbrviation of Suffix_Full.
Suffix_Cnty is also the abbrviation of Suffix_Full.
When Suffix_Bell is populated ( example: Rd(in Suffix_Bell) = Road(in Suffix_Full) Suffix_Cnty will be Null. and Vise Versa.
Suffix_Bell & Suffix_Cnty connot both be populated, its one or the other or niether.
The wierd part of this request is.
When Suffix_Cnty is not null i need to populate the Name field with
Prefix_Full + Street_Name + Suffix_Full (When Suffix_Cnty IS NOT NULL)
When Suffix_Bell is not null i need to populate the Name field with only
Prefix_Full + Street_Name. The Suffix_Bell Column gets put in the Type Field.
Which reading back i think i explained this part wrong to start. Sorry about that