Homework answers / question archive / Part III SQL code Job Code Analysis 1

Part III SQL code Job Code Analysis 1

Unrecognized

Share With

Part III SQL code Job Code Analysis 1.a generate table that summarize 2015 payroll data by job code: qry_Part3_Q1_2015Data SELECT a.Job_Code, a.Job_Description, b.Hours_2015 AS 2015_Hours, b.Gross_Pay2015 AS 2015_Gross_Pay, round( b.Gross_Pay_Per_Hour, 2) AS 2015_Gross_Pay_Per_Hour FROM tbl_JobCodes AS a LEFT JOIN (SELECT Job_Code, sum(Gross_Pay) AS Gross_Pay2015, sum (Gross_Hrs) AS Hours_2015, IIf(sum (Gross_Hrs) =0,0,sum(Gross_Pay)/ sum (Gross_Hrs) ) AS Gross_Pay_Per_Hour FROM qry_2015WithCleanJobCode GROUP BY Job_Code) AS b ON a.Job_Code = b.Job_Code; 1.b generate table that summarize 2016 payroll data by job code: qry_Part3_Q1_2016Data SELECT a.Job_Code, a.Job_Description, b.Hours_2016 AS 2016_Hours, b.Gross_Pay2016 AS 2016_Gross_Pay, Gross_Pay_Per_Hour AS 2016_Gross_Pay_Per_Hour FROM tbl_JobCodes AS a LEFT JOIN (SELECT Job_Code, sum(Gross_Wages) AS Gross_Pay2016, sum (Gross_Hrs) AS Hours_2016, round( sum(Gross_Wages)/ sum (Gross_Hrs), 2) AS Gross_Pay_Per_Hour FROM qry_2016WithCleanJobCode GROUP BY Job_Code) AS b ON a.Job_Code = b.Job_Code; 1.c generate table that compares difference between 2015 and 2016 summarized payroll data by job code: qry_2015_2016_JobDiff SELECT a.Job_Code, a.Job_Description, a.[2015_Hours], a.[2015_Gross_Pay], a.[2015_Gross_Pay_Per_Hour], b.[2016_Hours], b.[2016_Gross_Pay], b.[2016_Gross_Pay_Per_Hour], ( b.[2016_Hours] -a.[2015_Hours] ) AS Hours_Difference, round((b.[2016_Gross_Pay] - a.[2015_Gross_Pay] ) , 2) AS Gross_Pay_Difference, (b.[2016_Gross_Pay_Per_Hour] -a.[2015_Gross_Pay_Per_Hour] ) AS Gross_Pay_Per_Hour FROM qry_Part3_Q1_2015Data AS a LEFT JOIN qry_Part3_Q1_2016Data AS b ON a.Job_Code = b.Job_Code; Employee Level Analysis 2.a generate table that summarize 2015 payroll data by employee number: qry_Part3_Q2_2015Data SELECT a.Job_Code, b.Emp_No, b.Employee_Name, b.Hours_2015 AS 2015_Hours, b.Gross_Pay2015 AS 2015_Gross_Pay, round( b.Gross_Pay_Per_Hour, 2) AS 2015_Gross_Pay_Per_Hour FROM tbl_JobCodes AS a LEFT JOIN (SELECT Job_Code, Emp_No, Employee_Name, sum(Gross_Pay) AS Gross_Pay2015, sum (Gross_Hrs) AS Hours_2015, IIf(sum (Gross_Hrs) =0,0,sum(Gross_Pay)/ sum (Gross_Hrs) ) AS Gross_Pay_Per_Hour FROM qry_2015WithCleanJobCode GROUP BY Job_Code, Emp_No, Employee_Name) AS b ON a.Job_Code = b.Job_Code; 2.b generate table that summarize 2016 payroll data by employee number: qry_Part3_Q2_2016Data SELECT a.Job_Code, b.Employee_Number*1 AS Employee_Number, b.Employee_Name, b.Hours_2016 AS 2016_Hours, b.Gross_Pay2016 AS 2016_Gross_Pay, Round(b.Gross_Pay_Per_Hour,2) AS 2016_Gross_Pay_Per_Hour FROM tbl_JobCodes AS a LEFT JOIN (SELECT Job_Code, Employee_Number, Employee_Name, sum(Gross_Wages) AS Gross_Pay2016, sum (Hours) AS Hours_2016, IIf(sum (Gross_Hrs) =0,0,sum(Gross_Wages)/ sum (Gross_Hrs) ) AS Gross_Pay_Per_Hour FROM qry_2016WithCleanJobCode GROUP BY Job_Code, Employee_Number, Employee_Name) AS b ON a.Job_Code = b.Job_Code; 3.c generate table that compares difference between 2015 and 2016 summarized payroll data by job code: qry_2015_2016_EmpDiff SELECT a.Job_Code, a.Emp_No, a.Employee_Name, a.[2015_Hours], a.[2015_Gross_Pay], a.[2015_Gross_Pay_Per_Hour], b.[2016_Hours], b.[2016_Gross_Pay], b.[2016_Gross_Pay_Per_Hour], round((b.[2016_Hours]- a.[2015_Hours] ),2) AS Hours_Difference, round(( b.[2016_Gross_Pay] -a.[2015_Gross_Pay] ) , 2) AS Gross_Pay_Difference, round((b.[2016_Gross_Pay_Per_Hour] -a.[2015_Gross_Pay_Per_Hour] ),2) AS Gross_Pay_Per_Hour FROM qry_Part3_Q2_2015Data AS a LEFT JOIN qry_Part3_Q2_2016Data AS b ON (a.Employee_Name =b.Employee_Name) AND (a.Job_Code = b.Job_Code) WHERE a.[2015_Hours] >0 and a.[2015_Gross_Pay] >0 and b.[2016_Hours] >0 and b.[2016_Gross_Pay] >0 ORDER BY round(( b.[2016_Gross_Pay] -a.[2015_Gross_Pay] ) , 2) DESC; Find new employee SELECT distinct a.Employee_Number, a.employee_name FROM qry_2016WithCleanJobCode as a left JOIN qry_2015WithCleanJobCode as b ON a.Employee_Name = b.Employee_Name where a.Employee_Name <> b.Employee_Name; Tutorial Video: Submission Requirements: One Memo and One Access Database are required to submit. In the memo, it needs to address your own proposed questions in Part I. Further, you also need to show your analytic findings related to the payroll discrepancy based on the results of the queries you run. You need to address why there is a payroll gap between 2015 and 2016 based on the results from summary tables in Part III. As the 3rd point in Part III mentioned, please "Write a memo to Noah about what you have learned. Make sure to include the summary tables created by the queries above and any additional tables that you created in the memo. Discuss any discrepancies that you observed and make suggestions about what you recommend Noah should do next. " Additional tables are not required, but if you write new queries to create new tables, it will be a plus. Hence, the memo should include summary tables and explanations for the payroll gap, answers for proposed questions in Part I, discussions, and suggestions for the Noah. When you prepare the memo for Noah, please add analysis to address the payroll gap of $266,000. For example, in the job code level summary table, include the payroll difference between the 2015 and 2016 payroll for the job codes that contribute to the gap. Additionally, incorporate employee-level analysis for a more detailed examination. Identify significant pay changes among employees between 2015 and 2016.