S: SELECT TOP 50 H.HelpID, H.HelpHeading, H.HelpDesc, H.HelpDatePosted, H.HelpEndDate, H.HelpOrgType, H.HelpOrgID, H.HelpJobID, J.JobDesc, CASE WHEN (H.HelpCityActive='Y' AND CO.CityOrgID IS NOT NULL) THEN 1 WHEN (H.HelpOrgType='C' AND H.HelpOrgID = 7965) THEN 1 WHEN (H.HelpStateActive='Y' AND SO.StateOrgID IS NOT NULL) THEN 2 WHEN (H.HelpRegionActive='Y' AND RO.RegionOrgID IS NOT NULL) THEN 3 WHEN (H.HelpNationActive='Y' AND NAO.NationOrgID IS NOT NULL) THEN 4 ELSE 99 END AS Priority, CASE WHEN H.HelpOrgType='E' THEN ENT.EntityName WHEN H.HelpOrgType='C' THEN C.CityDesc WHEN H.HelpOrgType='T' THEN T.CountyDesc WHEN H.HelpOrgType='S' THEN S.StateDesc WHEN H.HelpOrgType='R' THEN R.RegionDesc WHEN H.HelpOrgType='N' THEN NAT.NationDesc WHEN H.HelpOrgType='H' THEN CBA.ClubName WHEN H.HelpOrgType='B' THEN BS.BoardName END AS OrgName FROM Helps H LEFT JOIN Jobs J ON H.HelpJobID = J.JobID LEFT JOIN Entitys ENT ON H.HelpOrgType='E' AND H.HelpOrgID=ENT.EntityID LEFT JOIN Citys C ON H.HelpOrgType='C' AND H.HelpOrgID=C.CityID LEFT JOIN Countys T ON H.HelpOrgType='T' AND H.HelpOrgID=T.CountyID LEFT JOIN States S ON H.HelpOrgType='S' AND H.HelpOrgID=S.StateID LEFT JOIN Regions R ON H.HelpOrgType='R' AND H.HelpOrgID=R.RegionID LEFT JOIN Nations NAT ON H.HelpOrgType='N' AND H.HelpOrgID=NAT.NationID LEFT JOIN Clubs CBA ON H.HelpOrgType='H' AND H.HelpOrgID=CBA.ClubID LEFT JOIN Boards BS ON H.HelpOrgType='B' AND H.HelpOrgID=BS.BoardID LEFT JOIN CityOrgs CO ON CO.COOrgType=H.HelpOrgType AND CO.COOrgID=H.HelpOrgID AND CO.COCityID=7965 LEFT JOIN StateOrgs SO ON SO.SOOrgType=H.HelpOrgType AND SO.SOOrgID=H.HelpOrgID AND SO.SOStateID=20 LEFT JOIN RegionOrgs RO ON RO.ROOrgType=H.HelpOrgType AND RO.ROOrgID=H.HelpOrgID AND RO.RORegionID=9 LEFT JOIN NationOrgs NAO ON NAO.NOOrgType=H.HelpOrgType AND NAO.NOOrgID=H.HelpOrgID AND NAO.NONationID=3 WHERE H.HelpActive='Y' AND DATEDIFF(day, H.HelpDatePosted, GETDATE()) <= 30AND H.HelpEndDate >= CONVERT(date, GETDATE()) AND ( (H.HelpCountyActive='Y' AND TOC.TOOrgID IS NOT NULL) OR (H.HelpOrgType='C' AND H.HelpOrgID = 7965) OR (H.HelpCityActive='Y' AND CO.COOrgID IS NOT NULL) OR (H.HelpStateActive='Y' AND SO.SOOrgID IS NOT NULL) OR (H.HelpRegionActive='Y' AND RO.ROOrgID IS NOT NULL) OR (H.HelpNationActive='Y' AND NAO.NOOrgID IS NOT NULL) ) AND HelpGovernmentActive = 'Y' ORDER BY Priority ASC, H.HelpID DESC
