Cacher is the code snippet organizer for pro developers

Select Top (@NumToReturn)
							a.chrProgName As SearchResult
							,0 As TotalJobs
						From Advert a WITH (NOLOCK)
						Inner Join AdvertClass acl WITH (NOLOCK) On acl.AdvertId = a.intAd_id
							And a.isActive = 1  
							And a.Hide = 0  
							And a.intType_id <> 8 
							And DATEDIFF(DAY, a.dtStartDate, GETDATE()) > -1
							And ( 
									(a.dtEndDate IS NULL) OR  
									(a.dtEndDate IS NOT NULL AND (a.dtEndDate >= GETDATE() AND a.dtEndDate >= a.dtStartDate)) 
								)
							And acl.ClassId = @SiteId
							and a.chrProgName Like (@Keyword + '%')
							Inner Join mdJobDetails jd WITH (NOLOCK) On jd.AdvertID = acl.AdvertId 
							Inner Join mdProfile p WITH (NOLOCK) On p.ProfileID = jd.ProfileID
							And p.Active = 1
						Group By a.chrProgName
						Order By COUNT(a.chrProgName) Desc