April 7, 2006 at 6:33 pm
Hi all,
I have 3 tabls:
1. job_level_table
Job_Code Job_Level Job_Title
3 Restaurant Level Assistant Manager
4 Restaurant Level General Manager
2. Job_Posting_Table
JobPosting_ID Job_Code Job_Title Job_Location
10 3 Assistant Manager BK12048
80 4 General Manager BK05662
82 3 Assistant Manager BK06444
87 4 General Manager BK13175
88 4 General Manager BK12048
3. Mgmt_Applicant_Table
Applicant_ID JobPosting_ID Applied_ZipCode Applied_Distance
20 77 47928 60
27 86 60067 30
28 3 53227 20
29 9 28645 5
30 10 28638 5
31 74 46341 20
I need a query to count how many applicants applied the job (based on job_postingid) in a certain driving distance.
The query works but the only problem is it count the applicants without considering the job_title.
For example, for Jobposting_id #10, the job_location = BK12048, job_title=Assistant Manager, there is an applicant#30 who applies this job, but the query will count 1 for Job_posting_ID=10 (An Assistant Manager job at BK12048) and count 1 for Job_Posting_ID=88 (A General Manager job at BK12048) because they both are same location (BK12048).
How to solve it in the query? Thank you.
select COALESCE(cnts.cnt, 0) As App_Count,J.Job_Code,j.Job_Title,j.Job_Location,j.JobPosting_ID,j.Job_ZipCode,l.Job_Level
from Job_Posting_Table j inner join Job_Level_Table l on j.Job_Code=l.Job_Code
left outer join (select count(*) cnt, J.JobPosting_ID
from Job_Posting_Table j
cross join Mgmt_Applicant_Table a
inner join Zips_Range_Table z1
on z1.Zip_Code = j.Job_ZipCode
inner join Zips_Range_Table 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 j.JobPosting_ID order by j.jobposting_id) cnts
on j.JobPosting_ID = cnts.JobPosting_ID WHERE (j.Job_Code <>7)
April 10, 2006 at 6:00 am
Given that the included query includes a bunch of stuff not seen in the sample data, I'm going to assume you're simplifying things. Hence, I'm not going to point out the normalization error of including the Job_Title in both the Job_Level_Table and the Job_Posting_Table. We'll just assume that it doesn't actually exist in the Job_Posting_Table.
Even as such, your method of querying is, to say the least, peculiar. I'm not really sure what your logic there is, especially including that cross join in the middle there.
SELECT JPT.JobPosting_ID, COUNT(MAT.Applicant_ID) AS App_Count
FROM Job_Posting_Table JPT
INNER JOIN Mgmt_Applicant_Table MAT
ON JPT.JobPosting_ID = MAT.JobPosting_ID
WHERE MAT.Applied_Distance > fZipDist(MAT.Applied_ZipCode)
GROUP BY JPT.JobPosting_ID
(fZipDist would be a UDF to do all the trigonometry on the Zips_Range_Table.)
That assumes that a single JobPosting_ID will only have one title. If not, you'll need to add a second join to the Job_Level_Table, and add Job_Level_Table.Job_Title to the GROUP BY statement.
Does that do what you need?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply