October 26, 2005 at 2:10 pm
Hi experts,
I am working on a data view to show HR people how many job applications we received from company's website and the job information for every job they posted out.
For the same job_code in same job_area, the number of applications should be the same. For example, there are 3 District Manager positions opening in Chicago North (the Job_Area), the one application submitted by applicant for this position should be count 1 in three openings.
I have 3 tables, Mgmt_Detail_Table (application details), job_posting_table, and job_level_table
Example data:
Mgmt_Detail_Table
Applicant_ID Received_Date Applied_Location JobPosting_ID
2 10/25/2005 Chicago North 9
3 10/26/2005 Southern Illiana 4
7 10/26/2005 Chicago North 9
5 10/26/2005 Southern Illiana 19
Job_Posting_Table
JobPosting_ID Job_Code Job_Title Job_Area
8 4 General Manager Chicago North
9 5 District Manager Chicago North
6 6 Regional Director Chicago South
4 5 District Manager Southern Illiana
7 5 District Manager Southern Illiana
19 5 District Manager Southern Illiana
Job_Level_Table
Job_Code Job_Level Job_Title
3 Restaurant Level Assistant Manager
4 Restaurant Level General Manager
5 District Level District Manager
6 Market Level Regional Director
The result dataset should be:
App_Count Job_Code Job_Title JobPosting_ID Job_Location Job_Level District_Name Job_Area
2 5 District Manager 4 District 1009 District Level District 1009 Southern Illiana
0 6 Regional Director 6 Chicago South Market Level Chicago South
2 5 District Manager 7 District 1008 District Level District 1008 Southern Illiana
0 4 General Manager 8 BK02672 Restaurant Level Chicago North
2 5 District Manager 9 District 1018 District Level District 1018 Chicago North
2 5 District Manager 19 District 1012 District Level District 1012 Southern Illiana
*The job area for job posting id 4 & 19 is in 'Southern Illiana' and both postings Job_Title are for District Manager, so for both job, the App_Count should be 2.
This is my code but is gives an error -Incorrect syntax near the keyword 'ON'.
SELECT ISNULL(M.App_Count, 0) AS App_Count, P.Job_Code, P.Job_Title, P.JobPosting_ID, P.Job_Location, P.Job_Posting_Date, P.Job_Start_Date,
L.Job_Level, P.District_Name, P.Job_Area, P.Job_Posting_Status
FROM (SELECT B.Job_Code, A.Applied_Location, COUNT(*) AS App_Count
FROM Mgmt_App_Detail_Table A INNER JOIN
ob_Posting_Table B ON A.JobPosting_ID = B.JobPosting_ID
GROUP BY B.Job_Code, A.Applied_Location) M RIGHT OUTER JOIN
Job_Level_Table L INNER JOIN
Job_Posting_Table P ON L.Job_Code = P.Job_Code ON L.Job_Code = M.Job_Code ON P.Job_Area=M.Job_Area
If I take out the last 'ON P.Job_Area=M.Job_Area', then query returns many duplicated records.....WHAT SHOULD I DO TO MY CODE SO THAT I CAN DISPLAY CORRECT RECORDS? THANK YOU IN ADVANCE.
October 26, 2005 at 4:42 pm
In the future, please post "CREATE TABLE" and Insert statments, not the output of a select.
There are a number of problems with your SQL:
1. Based the example tables and the SQL, the SQL shows other errors such as references to columns that do not exist.
2. You are expecting counts by Job_Code,Job_Title and Job_Location but have included JobPosting_ID in the result set.
3. The example data does not match the expected result.
Try this as a starting point:
SELECT COALESCE (M.App_Count, 0) AS App_Count
, P.Job_Code
, P.Job_Title
, Job_Level_Table.Job_Level
, P.Job_Area
FROM Job_Level_Table
JOIN(SELECT DISTINCT
Job_Posting_Table.Job_Code
, Job_Posting_Table.Job_Title
, Job_Posting_Table.Job_Area
from Job_Posting_Table
) as P
on P.Job_Code = Job_Level_Table.Job_Code
LEFT OUTER JOIN
(SELECT Job_Posting_Table.Job_Code
, Job_Posting_Table.Job_Title
, Job_Posting_Table.Job_Area
, COUNT(*) App_Count
FROM Job_Posting_Table
JOINMgmt_Detail_Table
On Mgmt_Detail_Table.JobPosting_ID = Job_Posting_Table.JobPosting_ID
GROUP BY Job_Posting_Table.Job_Code
, Job_Posting_Table.Job_Title
, Job_Posting_Table.Job_Area
) as M
ON M.Job_Code= P.Job_Code
AND M.Job_Title= P.Job_Title
ANDM.Job_Area= P.Job_Area
go
create table Job_Level_Table
( Job_Code integer not null
, Job_Level varchar(255) not null
, Job_Title varchar(255) not null
, constraint Job_Level_Table_P primary key (Job_Code)
, constraint Job_Level_Table_U_Job_Title unique (Job_Title)
)
go
create table Job_Posting_Table
( JobPosting_ID integer not null
, Job_Code integer not null
, Job_Title varchar(255) not null
, Job_Area varchar(255) not null
, constraint Job_Posting_Table_P primary key (JobPosting_ID)
, constraint Job_Level_Table_FK_Job_Posting_Table foreign key (Job_Code ) references Job_Level_Table
)
go
create table Mgmt_Detail_Table
( Applicant_ID integer not null
, Received_Date datetime not null
, Applied_Location varchar(255) not null
, JobPosting_IDintegernot null
, constraint Mgmt_Detail_Table_P primary key (Applicant_ID)
, constraint Job_Posting_Table_FK_Mgmt_Detail_Table foreign key (JobPosting_ID ) references Job_Posting_Table
)
go
insert into Job_Level_Table
(Job_Code ,Job_Level , Job_Title)
select 3 , 'Restaurant Level' ,'Assistant Manager' union all
select 4 , 'Restaurant Level' , 'General Manager' union all
select 5 , 'District Level' , 'District Manager' union all
select 6 , 'Market Level' , 'Regional Director'
go
insert into Job_Posting_Table
(JobPosting_ID , Job_Code , Job_Title , Job_Area)
select 8 , 4 , 'General Manager' ,'Chicago North' union all
select 9 , 5 , 'District Manager' ,'Chicago North' union all
select 6 , 6 , 'Regional Director','Chicago South' union all
select 4 , 5 , 'District Manager','Southern Illiana' union all
select 7 , 5 , 'District Manager','Southern Illiana' union all
select 19, 5 , 'District Manager','Southern Illiana'
go
insert into Mgmt_Detail_Table
(Applicant_ID , Received_Date, Applied_Location , JobPosting_ID)
select 2 , '10/25/2005' , 'Chicago North' , 9union all
select 3 , '10/26/2005' , 'Southern Illiana' , 4 union all
select 7 , '10/26/2005' , 'Chicago North' , 9 union all
select 5 , '10/26/2005' , 'Southern Illiana' , 19
go
SQL = Scarcely Qualifies as a Language
October 27, 2005 at 7:36 am
Carl, thank you so much. It works perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply