--

SELECT TOP(20) * FROM (SELECT row_number() OVER(ORDER by 10*dbo.MatchJobCategory(J.Category,1) + 30*dbo.MatchDate(J.PostedOn,180) + 10*(CASE WHEN 'Delhi' <> '' AND 'Delhi' = J.City THEN 1 ELSE babajob.dbo.MatchNearby(babajob.dbo.LatLongDistance(28.62832024,77.21047119,J.lat,J.lon),100) END) + 0 + 0 + 0 + 0 + 0 DESC) as rownum, 10*dbo.MatchJobCategory(J.Category,1) + 30*dbo.MatchDate(J.PostedOn,180) + 10*(CASE WHEN 'Delhi' <> '' AND 'Delhi' = J.City THEN 1 ELSE babajob.dbo.MatchNearby(babajob.dbo.LatLongDistance(28.62832024,77.21047119,J.lat,J.lon),100) END) + 0 + 0 + 0 + 0 + 0 as MatchRank, babajob.dbo.LatLongDistance(28.62832024,77.21047119,J.lat,J.lon) as Distance, J.*, U.FirstName + ' ' + U.LastName 'UserName', U.MobileVisibility, U.EmailVisibility, U.AddressVisibility, U.ProfileVisibility,U.PictureId 'ownerpic', CASE U.MobileVisibility WHEN 4 THEN U.Mobile# WHEN 12 THEN U.Mobile# WHEN 13 THEN U.Mobile# ELSE '' END AS 'Mobile#', CASE U.EmailVisibility WHEN 4 THEN U.EmailId WHEN 12 THEN U.EmailId WHEN 13 THEN U.EmailId ELSE '' END AS 'EmailId', CASE U.AddressVisibility WHEN 4 THEN U.CurrentAddress WHEN 12 THEN U.CurrentAddress WHEN 13 THEN U.CurrentAddress ELSE '' END AS 'CurrentAddress', U.Phone#, U.MobileDescription, U.PhoneDescription, c.JobCategoryDescription, t.TimingsDescription FROM [job] as j with (nolock) INNER JOIN [UserInfo] as u with (nolock) ON U.UserId = J.UserId INNER JOIN JobCategory as c with (nolock)ON J.Category = c.JobCategoryId INNER JOIN Timings as t with (nolock) ON t.TimingsId = J.Timing WHERE J.Visibility > 11 and J.isactive='true' and J.isdeleted='false' and year(EndingDate) <> 9999 AND (EndingDate > getdate() OR DATEDIFF(d,J.PostedOn,GETDATE()) <= 180) AND J.Country = 'India' AND (J.Type > 0 AND J.Type < 230) AND dbo.MatchJobCategory(J.Category,1)>0 AND J.city ='Delhi' ) as smstemp WHERE rownum>0