SOLVED

Need some help with Script

%3CLINGO-SUB%20id%3D%22lingo-sub-171380%22%20slang%3D%22en-US%22%3ENeed%20some%20help%20with%20Script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171380%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20requirement%20to%20pull%20data%20from%20a%20SQL%20database%20and%20into%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20really%20pretty%20simple%2C%20however%2C%20I%20am%20not%20skilled%20enough%20in%20writing%20the%20SQL%20Script%20to%20make%20this%20work.%3C%2FP%3E%0A%3CP%3EOne%20of%20the%20columns%20of%20data%20-%20Pincomecode%20in%20a%20table%20that%20has%203%20different%20results.%26nbsp%3B%20When%20the%20result%20in%20that%20column%20%3D%20GRPINS%20-%20I%20would%20like%20to%20have%20the%20result%20in%20a%20different%20column%20-%20GRPINSD.%26nbsp%3B%20When%20the%20value%20in%20the%20PIncomecode%20is%20Hourly%20or%20Salary%20then%20the%20result%20should%20be%20in%20a%20different%20column%20-%20RATED%3C%2FP%3E%0A%3CP%3EI%20am%20having%20a%20problem%20with%20the%20script%20to%20make%20this%20happen.%26nbsp%3B%20What%20I%20don't%20know%20how%20to%20do%20is%20push%20that%20result%20into%20a%20different%20column.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20need%20a%20real%20simple%20SELECT%20statement%20that%20will%20pull%20select%20data%20from%20the%20various%20tables%20and%20I%20will%20then%20use%20it%20to%20create%20the%20necessary%20VIEW%20or%20use%20the%20Excel%20Get%20and%20Transform%20function%20to%20finish%20it%20in%20Excel.%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20simple%20script%20so%20far%3A%20but%20I%20need%20to%20get%20the%20value%20r.rate%20into%20a%20different%20column%20based%20on%20the%20value%20of%20r.pincomecode.%26nbsp%3B%20I%20want%20only%20one%20line%20per%20PEmployeeID%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20I%20said%2C%20this%20is%20simple%20and%20I%20know%20you%20(all)%20might%20laugh%2C%20but%20I%20need%20to%20figure%20this%20out%20-%20so%20thanks%20in%20advance.%3C%2FP%3E%0A%3CP%3E*****%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eselect%20%3CBR%20%2F%3Ea.PEmployeeID%20as%20'EmployeeID'%2C%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3Ea.PFirstName%20as%20'FirstName'%2C%3CBR%20%2F%3Ea.PLastName%20as%20'LastName'%2C%3CBR%20%2F%3E(rtrim(a.PFirstName)%20%2B'%20'%2B%20rtrim(a.PLastName))%20as%20'EmployeeName'%2C%3C%2FP%3E%0A%3CP%3Ecase%20a.PContractor%3CBR%20%2F%3E%26nbsp%3Bwhen%201%20then%20'Contractor'%3CBR%20%2F%3E%26nbsp%3Bwhen%200%20then%20'Employee'%3CBR%20%2F%3EEnd%20as%20'Status'%2C%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3Ea.PDepartment%20as%20'DepartmentCode'%2C%3CBR%20%2F%3Eb.PDescription%20as%20'DepartmentName'%2C%3CBR%20%2F%3Ea.PStartDate%20as%20'StartDate'%2C%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23ff0000%22%3E(select%20r.PRate%20from%20cpy10140%20r%20%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23ff0000%22%3Ewhere%20r.pincomecode%20%3D'GRPINS'%20and%20r.pemployeeid%20%3D%20%40employid%20)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3Efrom%20CPY10100%20a%20%3CBR%20%2F%3Ejoin%20CPY10020%20b%20on%20a.PDepartment%20%3D%20b.PDepartment%3CBR%20%2F%3Ejoin%20CPY10140%20r%20on%20a.PEmployeeID%20%3D%20r.PEmployeeID%3CBR%20%2F%3Ewhere%20PInactive%20%3D%200%20%3CBR%20%2F%3Eand%20r.PIncomeCode%20in%20('GRPINS'%20%2C%20'HOURLY'%20%2C%20'SALARY')%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-171380%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBusiness%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-172499%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20Script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-172499%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F85523%22%20target%3D%22_blank%22%3E%40Bill%20Campbell%3C%2FA%3E%20wrote%3A%3CBR%20%2F%3E%3CP%3ESo%20this%20seems%20to%20work%2C%20and%20just%20so%20I%20understand%20what%20you%20did%2C%20you%20created%20a%20reference%20to%20the%20CPY10140%20table%20for%20each%20of%20the%20different%20values%20-%20RatedLine%20and%20GrpinsLine%20-%20and%20this%20allowed%20you%20to%20keep%20the%20result%20on%20a%20single%20line%20for%20each%20Employee%20ID%3C%2FP%3E%0A%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3EYes%2C%20except%20if%20the%20condition%20like%3C%2FP%3E%0A%3CPRE%3Eon%20a.PEmployeeID%20%3D%20ratedLine.PEmployeeID%20and%20ratedLine.PIncomeCode%20in%20('HOURLY'%20%2C%20'SALARY')%3C%2FPRE%3E%0A%3CP%3Ereturns%20more%20than%201%20line.%20In%20this%20case%20your%20%22EmployeeID%22%20line%20will%20be%20duplicated%20with%20each%26nbsp%3Bline%20joined.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegards%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYan%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-172258%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20Script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-172258%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20this%20seems%20to%20work%2C%20and%20just%20so%20I%20understand%20what%20you%20did%2C%20you%20created%20a%20reference%20to%20the%20CPY10140%20table%20for%20each%20of%20the%20different%20values%20-%20RatedLine%20and%20GrpinsLine%20-%20and%20this%20allowed%20you%20to%20keep%20the%20result%20on%20a%20single%20line%20for%20each%20Employee%20ID%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20you%20are%20correct%2C%20for%20each%20record%20in%20CPY10100%20there%20are%201%20or%20more%20entries%20in%20the%20CPY10140%20table.%26nbsp%3B%20I%20should%20have%20been%20clearer%20on%20that%20when%20I%20started.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYan%2C%20thanks%20very%20much%20for%20this%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-171991%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20Script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171991%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20think%20for%201%20employee%20(in%20table%26nbsp%3BCPY10100%20I%20suppose)%2C%20you%20can%20have%200%20or%20more%26nbsp%3BCPY10140%20lines.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20case%20is%20more%2C%20you%20can%20try%20this%2C%20but%20because%20I%20don't%20know%20your%20schema%20nor%20your%20datas%2C%20you%20can%20have%26nbsp%3Bmultiple%20lines%20for%20each%20employee%20if%20in%20CPY10140%20table%20you%20have%20multiple%20lines%20for%20each%20PIncomeCode%20for%26nbsp%3Bone%20employee%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Eselect%20%0A%20a.PEmployeeID%20as%20'EmployeeID'%2C%0A%20a.PFirstName%20as%20'FirstName'%2C%0A%20a.PLastName%20as%20'LastName'%2C%0A%20(rtrim(a.PFirstName)%20%2B'%20'%2B%20rtrim(a.PLastName))%20as%20'EmployeeName'%2C%0A%20case%20a.PContractor%0A%20%20when%201%20then%20'Contractor'%0A%20%20when%200%20then%20'Employee'%0A%20End%20as%20'Status'%2C%0A%20a.PDepartment%20as%20'DepartmentCode'%2C%0A%20b.PDescription%20as%20'DepartmentName'%2C%0A%20a.PStartDate%20as%20'StartDate'%2C%0A%20ratedLine.PRate%20as%20RATED%2C%0A%20grpinsLine.PRate%20as%20GRPINSD%0Afrom%20CPY10100%20a%20%0A%20join%20CPY10020%20b%20on%20a.PDepartment%20%3D%20b.PDepartment%0A%20left%20join%20CPY10140%20ratedLine%20on%20a.PEmployeeID%20%3D%20ratedLine.PEmployeeID%20and%20ratedLine.PIncomeCode%20in%20('HOURLY'%20%2C%20'SALARY')%0A%20left%20join%20CPY10140%20grpinsLine%20on%20a.PEmployeeID%20%3D%20grpinsLine.PEmployeeID%20and%20grpinsLine.PIncomeCode%20%3D%20'GRPINS'%0Awhere%20PInactive%20%3D%200%20%0A%3C%2FPRE%3E%0A%3CP%3ERegards%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYan%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-171889%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20Script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171889%22%20slang%3D%22en-US%22%3E%3CP%3EOk%2C%20so%20that%20is%20so%20close.%26nbsp%3B%20It%20gets%20the%20value%20for%20r.PRate%20into%20two%20different%20columns%20-%20perfect%20-%20however%20-%20I%20get%20at%20least%20two%20lines%20per%20Employee%20ID%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20really%20need%20to%20just%20have%20the%20single%20line%20for%20each%20employee.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20so%20much%20for%20getting%20me%20this%20far.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-171577%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20Script%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171577%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdding%20the%20following%20'case'%20in%20your%20SELECT%20clause%2C%26nbsp%3Bhelp%20you%20%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%20case%0A%20%20when%20r.pincomecode%20%3D'GRPINS'%20then%20r.PRate%0A%20%20else%20null%0A%20end%20as%20GRPINSD%2C%0A%20case%0A%20%20when%20r.pincomecode%20in%20('HOURLY'%2C%20'SALARY')%20then%20r.PRate%0A%20%20else%20null%0A%20end%20as%20RATED%0A%3C%2FPRE%3E%0A%3CP%3ERegards%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYan%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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')

5 Replies
Highlighted

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

Highlighted

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.

Highlighted
Solution

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

 

 

Highlighted

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.

Highlighted

@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