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
Hi,
Adding the following 'case' in your SELECT clause, help you ?
case
when r.pincomecode ='GRPINS' then r.PRate
else null
end as GRPINSD,
case
when r.pincomecode in ('HOURLY', 'SALARY') then r.PRate
else null
end as RATED
Regards,
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 = 0Regards,
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