I have the following code
SELECT PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI,
PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed,
TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount,
FROM PayEmployeeMaster INNER JOIN
TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND
PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND
PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
Which brings back records for the last weeks pay. The problem I have is if the employee has been on shifts or overtime then it brings back several records as the PayEmployeePayCodes table has a record for each shift and hours worked on that shift.
Ideally if possible I would like to return the PayEmployeePayCodes.PayCod
e as a column header and the PayEmployeePayCodes.InputV
alue as the value under each PayCode.
I have attached a file with the records returned for one employee with the records as they are returned and the layout I would like to get to.
Any ideas ?