Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
wldodds
Starting Member
20 Posts |
Posted - 2013-01-16 : 17:20:05
|
I have a table which contains 3 columns (empid, benefit, amount). The primary keys are empid and benefit. An Employee can have many different benefits with different amounts.empid_i benefit costemployee_i1331 04000 3.260001331 10101 0.000001331 13100 0.000001331 14140 2.620001331 15120 3.595401331 16100 0.000001331 21610 4.26920I'm trying to get specific benefits into specific columns. For example I want anything beginning with '14' to be in medcolumn and anything beginning with 15 to be in dencolumn.Here is the code I'm using but it returns 2 rows for the employee instead of 1 row with the amounts in the specific columns.SELECT ebm.EMPID_I as Employee_Id, (case when left(med.benefit,2) = ('14') then med.COSTEMPLOYEE_I else 0 end) as Medical_Amount, (case when left(den.benefit,2) = ('15') then den.COSTEMPLOYEE_I else 0 end) as Dental_AmountFROM BE010130 ebm (nolock) inner join BE010130 med (nolock) on ebm.EMPID_I = med.EMPID_I and ebm.BENEFIT = med.BENEFIT inner join BE010130 den (nolock) on ebm.EMPID_I = den.EMPID_I and ebm.BENEFIT = den.BENEFITWHERE ebm.BENEFITSTATUS_I = 1 -- Active Only and ebm.BENEFIT in (select BENEFIT from BE020230 ben (nolock) where (ben.DSCRIPTN LIKE 'D/ UHC%' or ben.DSCRIPTN LIKE 'D/ Dental%')) and ebm.empid_i = 1331Any help is greatly appreciated.Thanks, |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-16 : 22:14:49
|
[code]SELECT ebm.EMPID_I as Employee_Id, MAX(Case when left(med.benefit,2) = ('14') then med.COSTEMPLOYEE_I else 0 end) as Medical_Amount,MAX(Case when left(den.benefit,2) = ('15') then med.COSTEMPLOYEE_I else 0 end) as Dental_AmountFROM BE010130 ebm (nolock)inner join BE010130 med (nolock) on ebm.EMPID_I = med.EMPID_I and ebm.BENEFIT = med.BENEFITWHERE ebm.BENEFITSTATUS_I = 1 -- Active Onlyand ebm.BENEFIT in (select BENEFIT from BE020230 ben (nolock) where (ben.DSCRIPTN LIKE 'D/ UHC%' or ben.DSCRIPTN LIKE 'D/ Dental%'))and ebm.empid_i = 1331Group by ebm.EMPID_I[/code] |
|
|
|
|
|