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='N' AND H.HelpOrgID = 3) 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 NationOrgs NAO ON NAO.NOOrgType=M.HelpOrgType AND NAO.NOOrgID=M.HelpOrgID AND NAO.NONationID=3 WHERE M.HelpActive='Y' AND AND DATEDIFF(day, dbo.Helps.HelpDatePosted, GETDATE()) <= AND (M.HelpNationActive='Y' AND NAO.NationOrgID IS NOT NULL) OR (H.HelpOrgType='N' AND H.HelpOrgID = 3) AND HelpHealthcareActive = 'Y' ORDER BY Priority ASC, H.HelpID DESC
