--

SELECT TOP(20) * FROM (SELECT row_number() OVER(ORDER by 10*dbo.MatchJobCategory(J.Category,19) + 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,19) + 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.JobID,J.UserID,J.Title,J.AddReq,J.Gender,J.Timing,J.Pay,J.Address,J.City,J.State,J.Country,J.PinCode,J.Description,J.PostedOn,J.YearExperience,J.PaymentStatus,J.FillingStatus,J.Lat,J.Lon,J.Zoom,J.Employee#,J.Type,J.PictureID,J.IsDeleted,J.EndingDate,J.Category,J.Visibility,J.OwnerVisibility,J.IsActive,J.TotalRequest,J.AcceptedRequest,J.DeclinedRequest,J.Footer,J.JobPlanId,J.DesiredJobPlanId,J.AlertType,J.SMSCount,J.SMSMax,J.JobSourceId,J.CreatedByID,J.CustomerRepID,J.ModifiedDate,J.ModifiedByID , 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 > '2012-05-27 00:00:00.000' OR J.PostedOn > '2011-11-29 00:00:00.000') AND J.Country = 'India' AND (J.Type > 0 AND J.Type < 230) AND J.Category IN (19) AND J.city ='Delhi' ) as smstemp WHERE rownum>20