Forum Discussion
Solved
Need some help with Script
I have a requirement to pull data from a SQL database and into Excel.
It is really pretty simple, however, I am not skilled enough in writing the SQL Script to make this work.
One of the columns of data - Pincomecode in a table that has 3 different results. When the result in that column = GRPINS - I would like to have the result in a different column - GRPINSD. When the value in the PIncomecode is Hourly or Salary then the result should be in a different column - RATED
I am having a problem with the script to make this happen. What I don't know how to do is push that result into a different column.
I need a real simple SELECT statement that will pull select data from the various tables and I will then use it to create the necessary VIEW or use the Excel Get and Transform function to finish it in Excel.
Here is the simple script so far: but I need to get the value r.rate into a different column based on the value of r.pincomecode. I want only one line per PEmployeeID
Like I said, this is simple and I know you (all) might laugh, but I need to figure this out - so thanks in advance.
*****
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',
(select r.PRate from cpy10140 r
where r.pincomecode ='GRPINS' and r.pemployeeid = @employid )
from CPY10100 a
join CPY10020 b on a.PDepartment = b.PDepartment
join CPY10140 r on a.PEmployeeID = r.PEmployeeID
where PInactive = 0
and r.PIncomeCode in ('GRPINS' , 'HOURLY' , 'SALARY')
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 RATEDRegards,
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
