S: SELECT TOP 50 SP.SpecialID, SP.SpecialHeading, SP.SpecialDesc, SP.SpecialBeginDate, SP.SpecialEndDate, SP.SpecialDatePosted, SP.SpecialOrgType, SP.SpecialOrgID, CASE WHEN (SP.SpecialOrgType='C' AND SP.SpecialCityActive='Y' AND SP.SpecialOrgID=21319) THEN 1 WHEN (SP.SpecialCityActive='Y' AND CO.COCityID IS NOT NULL) THEN 1 WHEN (SP.SpecialStateActive='Y' AND SO.SOStateID IS NOT NULL) THEN 2 WHEN (SP.SpecialRegionActive='Y' AND RO.RORegionID IS NOT NULL) THEN 3 WHEN (SP.SpecialNationActive='Y' AND NAO.NONationID IS NOT NULL) THEN 4 ELSE 99 END AS Priority, CASE WHEN SP.SpecialOrgType='E' THEN ENT.EntityName WHEN SP.SpecialOrgType='C' THEN C.CityDesc WHEN SP.SpecialOrgType='T' THEN T.CountyDesc WHEN SP.SpecialOrgType='S' THEN S.StateDesc WHEN SP.SpecialOrgType='R' THEN R.RegionDesc WHEN SP.SpecialOrgType='N' THEN NAT.NationDesc WHEN SP.SpecialOrgType='H' THEN CBA.ClubName WHEN SP.SpecialOrgType='B' THEN BS.BoardName END AS OrgName FROM Specials SP LEFT JOIN Entitys ENT ON SP.SpecialOrgType='E' AND SP.SpecialOrgID=ENT.EntityID LEFT JOIN Citys C ON SP.SpecialOrgType='C' AND SP.SpecialOrgID=C.CityID LEFT JOIN Countys T ON SP.SpecialOrgType='T' AND SP.SpecialOrgID=T.CountyID LEFT JOIN States S ON SP.SpecialOrgType='S' AND SP.SpecialOrgID=S.StateID LEFT JOIN Regions R ON SP.SpecialOrgType='R' AND SP.SpecialOrgID=R.RegionID LEFT JOIN Nations NAT ON SP.SpecialOrgType='N' AND SP.SpecialOrgID=NAT.NationID LEFT JOIN Clubs CBA ON SP.SpecialOrgType='H' AND SP.SpecialOrgID=CBA.ClubID LEFT JOIN Boards BS ON SP.SpecialOrgType='B' AND SP.SpecialOrgID=BS.BoardID LEFT JOIN CityOrgs CO ON CO.COOrgType=SP.SpecialOrgType AND CO.COOrgID=SP.SpecialOrgID AND CO.COCityID=21319 LEFT JOIN StateOrgs SO ON SO.SOOrgType=SP.SpecialOrgType AND SO.SOOrgID=SP.SpecialOrgID AND SO.SOStateID=41 LEFT JOIN RegionOrgs RO ON RO.ROOrgType=SP.SpecialOrgType AND RO.ROOrgID=SP.SpecialOrgID AND RO.RORegionID=11 LEFT JOIN NationOrgs NAO ON NAO.NOOrgType=SP.SpecialOrgType AND NAO.NOOrgID=SP.SpecialOrgID AND NAO.NONationID=3 WHERE SP.SpecialActive='Y' AND SP.SpecialDisplayActive='Y' AND DATEDIFF(day, SP.SpecialDatePosted, GETDATE()) <= 30 AND SP.SpecialBeginDate <= CONVERT(date, GETDATE()) AND SP.SpecialEndDate >= CONVERT(date, GETDATE()) AND ( (SP.SpecialOrgType='C' AND SP.SpecialCityActive='Y' AND SP.SpecialOrgID=21319) OR (SP.SpecialCityActive='Y' AND CO.COCityID IS NOT NULL) OR (SP.SpecialStateActive='Y' AND SO.SOStateID IS NOT NULL) OR (SP.SpecialRegionActive='Y' AND RO.RORegionID IS NOT NULL) OR (SP.SpecialNationActive='Y' AND NAO.NONationID IS NOT NULL) ) AND SpecialTrainingActive = 'Y' ORDER BY Priority ASC, SP.SpecialID DESC