Forum Discussion
Need some help with Script
So I think for 1 employee (in table CPY10100 I suppose), you can have 0 or more CPY10140 lines.
This case is more, you can try this, but because I don't know your schema nor your datas, you can have multiple lines for each employee if in CPY10140 table you have multiple lines for each PIncomeCode for one employee:
select a.PEmployeeID as 'EmployeeID', a.PFirstName as 'FirstName', a.PLastName as 'LastName', (rtrim(a.PFirstName) +' '+ rtrim(a.PLastName)) as 'EmployeeName', case a.PContractor when 1 then 'Contractor' when 0 then 'Employee' End as 'Status', a.PDepartment as 'DepartmentCode', b.PDescription as 'DepartmentName', a.PStartDate as 'StartDate', ratedLine.PRate as RATED, grpinsLine.PRate as GRPINSD from CPY10100 a join CPY10020 b on a.PDepartment = b.PDepartment left join CPY10140 ratedLine on a.PEmployeeID = ratedLine.PEmployeeID and ratedLine.PIncomeCode in ('HOURLY' , 'SALARY') left join CPY10140 grpinsLine on a.PEmployeeID = grpinsLine.PEmployeeID and grpinsLine.PIncomeCode = 'GRPINS' where PInactive = 0Regards,
Yan
Ok, so that is so close. It gets the value for r.PRate into two different columns - perfect - however - I get at least two lines per Employee ID
I really need to just have the single line for each employee.
Thanks so much for getting me this far.
So I think for 1 employee (in table CPY10100 I suppose), you can have 0 or more CPY10140 lines.
This case is more, you can try this, but because I don't know your schema nor your datas, you can have multiple lines for each employee if in CPY10140 table you have multiple lines for each PIncomeCode for one employee:
select
a.PEmployeeID as 'EmployeeID',
a.PFirstName as 'FirstName',
a.PLastName as 'LastName',
(rtrim(a.PFirstName) +' '+ rtrim(a.PLastName)) as 'EmployeeName',
case a.PContractor
when 1 then 'Contractor'
when 0 then 'Employee'
End as 'Status',
a.PDepartment as 'DepartmentCode',
b.PDescription as 'DepartmentName',
a.PStartDate as 'StartDate',
ratedLine.PRate as RATED,
grpinsLine.PRate as GRPINSD
from CPY10100 a
join CPY10020 b on a.PDepartment = b.PDepartment
left join CPY10140 ratedLine on a.PEmployeeID = ratedLine.PEmployeeID and ratedLine.PIncomeCode in ('HOURLY' , 'SALARY')
left join CPY10140 grpinsLine on a.PEmployeeID = grpinsLine.PEmployeeID and grpinsLine.PIncomeCode = 'GRPINS'
where PInactive = 0
Regards,
Yan
So this seems to work, and just so I understand what you did, you created a reference to the CPY10140 table for each of the different values - RatedLine and GrpinsLine - and this allowed you to keep the result on a single line for each Employee ID
By the way, you are correct, for each record in CPY10100 there are 1 or more entries in the CPY10140 table. I should have been clearer on that when I started.
Yan, thanks very much for this assistance.
Bill Campbell wrote:
So this seems to work, and just so I understand what you did, you created a reference to the CPY10140 table for each of the different values - RatedLine and GrpinsLine - and this allowed you to keep the result on a single line for each Employee ID
Yes, except if the condition like
on a.PEmployeeID = ratedLine.PEmployeeID and ratedLine.PIncomeCode in ('HOURLY' , 'SALARY')returns more than 1 line. In this case your "EmployeeID" line will be duplicated with each line joined.
Regards,
Yan