SELECT TOP 200 L.LatestID, L.LatestHeading, L.LatestDesc, L.LatestDatePosted, L.LatestOrgType, L.LatestOrgID, CASE WHEN (L.LatestOrgType='C' AND L.LatestOrgID=1576) THEN 1 WHEN (L.LatestCityActive='Y') THEN 1 WHEN (L.LatestCountyActive='Y') THEN 1 WHEN (L.LatestStateActive='Y') THEN 2 WHEN (L.LatestRegionActive='Y') THEN 3 WHEN (L.LatestNationActive='Y') THEN 4 ELSE 99 END AS Priority, CASE WHEN L.LatestOrgType='E' THEN ENT.EntityName WHEN L.LatestOrgType='C' THEN C.CityDesc WHEN L.LatestOrgType='T' THEN T.CountyDesc WHEN L.LatestOrgType='S' THEN S.StateDesc WHEN L.LatestOrgType='R' THEN R.RegionDesc WHEN L.LatestOrgType='N' THEN NAT.NationDesc WHEN L.LatestOrgType='H' THEN CBA.ClubName WHEN L.LatestOrgType='B' THEN BS.BoardName END AS OrgName FROM Latests L LEFT JOIN Entitys ENT ON L.LatestOrgType='E' AND L.LatestOrgID=ENT.EntityID LEFT JOIN Citys C ON L.LatestOrgType='C' AND L.LatestOrgID=C.CityID LEFT JOIN Countys T ON L.LatestOrgType='T' AND L.LatestOrgID=T.CountyID LEFT JOIN States S ON L.LatestOrgType='S' AND L.LatestOrgID=S.StateID LEFT JOIN Regions R ON L.LatestOrgType='R' AND L.LatestOrgID=R.RegionID LEFT JOIN Nations NAT ON L.LatestOrgType='N' AND L.LatestOrgID=NAT.NationID LEFT JOIN Clubs CBA ON L.LatestOrgType='H' AND L.LatestOrgID=CBA.ClubID LEFT JOIN Boards BS ON L.LatestOrgType='B' AND L.LatestOrgID=BS.BoardID WHERE L.LatestActive='Y' AND L.LatestDisplayActive='Y' AND L.LatestDatePosted >= DATEADD(day, -15, GETDATE()) AND ( (L.LatestCityActive='Y') OR (L.LatestOrgType='C' AND L.LatestOrgID=1576) OR (L.LatestCountyActive='Y') OR (L.LatestStateActive='Y') OR (L.LatestRegionActive='Y') OR (L.LatestNationActive='Y') ) AND LatestTeensActive = 'Y' ORDER BY Priority ASC, L.LatestID DESC
