I need help on how to write the stored procedure for my report!! PLEASE HELP!
-
Hi experts.
I have 3 tables in SQL Server 2000. I need to generate a report to HR department and show them how many applicants in applicant_table is available for each job that HR posted in the job_Posting_table,
based on applicant's applied zipcode and the radius the applicant is willing to drive to.
The stored procedure at below can help to find out all zipcodes (or a list of jobs) an applicant is available to.
Zips_Range_Table (The Zips_Range_Table is loaded from postal office system, it contains a countrywide zip codes/longitude and latitude.)
--------------------------
Zip_Code Longitude Latitude
35004 86.51557 33.584132
35005 86.959727 33.588437
35006 87.167455 33.434277
Job_Posting_Table (HR department posts opening jobs to Job_Posting_Table)
-----------------------
JobPosting_ID Job_Code Job_Title Job_Location Job_ZipCode
1 4 General Manager BK03261 53018
2 4 General Manager BK07445 29303
3 3 Assistant Manager BK00426 53410
4 3 Assistant Manager BK04678 53406
Appliant_Table : Master applicant table contains applicant ID, applied job ID, searched jobs within a driving distance (in miles) (Applied_Disatnce) of a Zipcode (Applied_ZipCode)
----------------------------------------------------
App_ID JobPosting_ID Applied_ZipCode Applied_Distance
1 1 53026 20
2 1 53009 30
4 1 53014 30
5 3 53412 20
6 4 53406 20
9 2 29300 20
When applicant searches opening jobs on company's website from our database based on the driving distance of a zipcode he entered, then the stored procedure will get all distinct cities of our availiable jobs in our job_Posting_Table:
here is the sp (I found the stored procedure with basic logic from a website):
CREATE Procedure sp_ZipCode_FindJobs
(
@ZipCode Numeric(5, 0) = Null,
@Miles Float
 
As
set nocount on
Declare @Latitude Float(10)
Declare @Longitude Float(10)
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From Zips_Range_Table Where Zip_Code = @ZipCode
Select
Max( j.Job_Posting_Date) as Job_Posting_Date,j.Job_Title, Max( j.JobPosting_ID ) as JobPosting_ID
From
Zips_Range_Table z INNER JOIN
Job_Posting_Table j ON z.Zip_Code = j.Job_ZipCode
Where
z.Longitude Is Not Null
And z.Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(z.Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(z.Latitude/57.2958) *
Cos(z.Longitude/57.2958 - @Longitude/57.2958))
)
And z.Zip_Code != @ZipCode
group by j.Job_Title
return
GO
----------------------------------------------------------------------------------------------------------
My question is: I need to have a query or a stored procedure to calculate how many applicants is available for each job posted in the job_posting_table.
For example, Applicant #5 applied the "Assistant Manager" job and he is willing to drive 20 miles in the radius of a zipcode 53412. And within 20 miles of Zipcode 53412,
this applicant is available for job posting ID #3 and #4.
So the result should look like this:
Received JobPosting_ID Job_Title Job_Location Job_ZipCode
Applicants
3 1 General Manager BK03261 53018
1 2 General Manager BK07445 29303
2 3 Assistant Manager BK00426 53410
2 4 Assistant Manager BK04678 53406
PLEASE GUIDE ME HOW TO GENERATE SUCH REPORT! PLEASE HELP!! THANK YOU!!
-
This is untested because the zip data does not match the job and applicant data but try something like this:
drop table Zip
go
create table Zip(Zip_Code int, Longitude decimal(10,6), Latitude decimal(10,6))
go
insert Zip values (35004, 86.51557, 33.584132)
insert Zip values (35005, 86.959727, 33.588437)
insert Zip values (35006, 87.167455, 33.434277)
go
drop function distance
go
create function distance(@zip1 int, @zip2 int)
returns decimal(10,6)
as
begin
return (select 3958.75 * ACos(Sin(z1.Latitude/57.2958) *
Sin(z2.Latitude/57.2958) +
Cos(z1.Latitude/57.2958) *
Cos(z2.Latitude/57.2958) *
Cos(z2.Longitude/57.2958 - z1.Longitude/57.2958))
from Zip z1, Zip z2
where z1.Zip_Code = @zip1 and z2.Zip_Code = @zip2
)
end
go
select count(*) 'Received Applicants', jp.JobPosting_ID, jp.Job_Title, jp.Job_Location, jp.Job_ZipCode
from Job_Posting_Table jp
cross join Appliant_Table a
where distance(jp.Job_ZipCode, a.Applied_ZipCode) < a.Applied_Distanct
group by jp.JobPosting_ID, jp.Job_Title, jp.Job_Location, jp.Job_ZipCode
go
-
THNK YOU JEFF! It works great! except the result returns only the applicant available for the job, I need it to display zero in 'Received Applicants' when this job doesn't have any available applicant.
I changed count(*) to COALESCE (count(*),0) , but it still not working???? Please help again.
-----------------------------------------------------------------------------------
select COALESCE (count(*),0) as 'Received Applicants', jp.JobPosting_ID, jp.Job_Title, jp.Job_Location, jp.Job_ZipCode
from Job_Posting_Table jp
cross join dbo.Mgmt_Applicant_Table a
where dbo.distance(jp.Job_ZipCode, a.Applied_ZipCode) <= a.Applied_Distance
group by jp.JobPosting_ID, jp.Job_Title, jp.Job_Location, jp.Job_ZipCode
-------------------------------------------------------------------------------------
-
Try this:
select COALESCE(cnts.cnt, 0) 'Received Applicants', j.JobPosting_ID, j.Job_Title, j.Job_Location, j.Job_ZipCode
from Job_Posting_Table j
left outer join (select count(*) cnt, jp.JobPosting_ID
from Job_Posting_Table jp
cross join Appliant_Table a
where distance(jp.Job_ZipCode, a.Applied_ZipCode) < a.Applied_Distanct
group by jp.JobPosting_ID) cnts
on j.JobPosting_ID = cnts.JobPosting_ID
-
Perfect! THANK YOU THANK YOU THANK YOU! THANK YOU SO MUCH!
-
Very nice...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The code is neat and great, but now I am running into performance issue. The result returns in average 8 seconds, and all the tables are less than 70 rows. The Zips_Range_Table in Distance Function has 29470 rows and I added an index on Zip_Code. The index doesn't help after I tested it.
The code is used for our HR asp.net application, the performance is very important at this point.
Any suggestions?
-
Some basic optimzation techniques to check:
Are the datatypes for each column used in a join the same? Data conversion causes indexing problems.
Are there indexes indexes on JobPosting_ID in both tables (jobs and applicants)?
Try running just this part and see how long it takes:
select count(*) cnt, jp.JobPosting_ID
from Job_Posting_Table jp
cross join Appliant_Table a
where distance(jp.Job_ZipCode, a.Applied_ZipCode) < a.Applied_Distanct
group by jp.JobPosting_ID
Try this and see if you get the same results:
select count(*) cnt, jp.JobPosting_ID
from Job_Posting_Table jp
inner join Appliant_Table a
on distance(jp.Job_ZipCode, a.Applied_ZipCode) < a.Applied_Distanct
group by jp.JobPosting_ID
-
Yes I checked the datatypes for those joined columns, they are the same.
I have indexes on both jobs table and applicants table.
And I got same result :8 seconds , from both queries (cross join and inner join)
-
Try this:
select COALESCE(cnts.cnt, 0) 'Received Applicants', jp.JobPosting_ID, jp.Job_Title, jp.Job_Location, jp.Job_ZipCode
from Job_Posting_Table jp
left outer join (select count(*) cnt, jp.JobPosting_ID
from Job_Posting_Table jp
cross join Applicant_Table a
inner join Zip z1
on z1.Zip_Code = jp.Job_ZipCode
inner join zip z2
on z2.Zip_Code = a.Applied_ZipCode
where 3958.75 * acos(round(Sin(z1.Latitude/57.2958) *
Sin(z2.Latitude/57.2958) +
Cos(z1.Latitude/57.2958) *
Cos(z2.Latitude/57.2958) *
Cos(z2.Longitude/57.2958 - z1.Longitude/57.2958), 6)) < a.Applied_Distance
group by jp.JobPosting_ID) cnts
on jp.JobPosting_ID = cnts.JobPosting_ID
-
super! Thanks a million!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply