Join in a complex query

Last Post 18 Apr 2006 12:29 PM by nosepicker. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
natasha
New Member
New Member

--
18 Apr 2006 10:20 AM
create table #temp1
(Compid int,
DeptID int,
StatusCode varchar(50),
StcodeDesc varchar(100),
Salary int,
SpendLimit int)

create table #temp1_lkp
(CompID int,
FieldName varchar(100),
StatusCode Varchar(50),
stcodedesc varchar(100))

insert #temp1
values
(111,123,'E','Exempt',300,100)
insert #temp1
values
(111,123,'M','Manager',1000,500)
insert #temp1
values
(111,123,'vp','vicepres',5000,1000)
insert #temp1
values
(111,123,'WP','VP',5000,1000)
--select * from #temp1


insert #temp1_lkp
values
(111,'ExemptEmp','E','EXT')
insert #temp1_lkp
values
(111,'ExemptEmp','Exmpt','Exmp-Emp')
insert #temp1_lkp
values
(111,'ExemptEmp','Empt','Xempt')
insert #temp1_lkp
values
(111,'Supervisors','Sup','Supervisor')
insert #temp1_lkp
values
(111,'Supervisors','S','Super')
insert #temp1_lkp
values
(111,'Supervisors','SP','SUP-Visor')
insert #temp1_lkp
values
(111,'Managers','M','Manage')
insert #temp1_lkp
values
(111,'Managers','mgr','Managers')
insert #temp1_lkp
values
(111,'Management','VP','ViceP')
insert #temp1_lkp
values
(111,'Management','WP','VP')
insert #temp1_lkp
values
(111,'Management','P','President')
insert #temp1_lkp
values
(111,'Management','pres','Presidente')

--select * from #temp1_lkp

select deptid,
max(case when statuscode in ('E','Exmpt','Empt','Exem') -- I would like to use values from lookup table instead of hardcoding based on compID
then Statuscode
else null end) as ExemptEmp,
max(case when statuscode in ('SUP','S','SP')
then Statuscode
else null end) as Supervisors,
sum(case when statuscode in ('SUP','S','SP')
then salary
else null end) as TotSupSalary,
max(case when statuscode in ('M','MGR')
then Statuscode
else null end) as managers,
max(case when statuscode in ('VP','WP','P','PRES')
then Statuscode
else null end) as management
from #temp1
group by deptid

Please run the query to see the results. In short I am changing data from rows to columns. If there are multiple codes for the same deptid then I pick the max code.

The above query is working but I can have about gazzillion company codes. The empstatus code and its description might be different in each companies. I have created a lookup table for all the possible codes for all the possible companies. My dilema is how do I use the values form the lookup table in a "IN" clause in order to avoid hardcoding the codes?
Trust me I have tried to use top, Subquery etc.... but I am missing something ...It is just not working.

Thank you for any and all help,
Nat
nosepicker
New Member
New Member

--
18 Apr 2006 12:29 PM
In your lookup table, can you create a separate column, something like a group id? Then you can give certain status codes the same groupid (like 'E', 'Exmpt', 'Empt', 'Exem' = 1, etc.). Then, you can do something like this:

SELECT deptid,
CASE WHEN lookup.groupid = 1 THEN #temp1.StatusCode ELSE NULL END AS ExemptEmp,
CASE WHEN lookup.groupid = 2 THEN #temp1.StatusCode ELSE NULL END AS Supervisors,
...
FROM #temp1 JOIN lookup ON #temp1.statuscode = lookup.statuscode
GROUP BY deptid
natasha
New Member
New Member

--
19 Apr 2006 05:46 AM
Thank you so much. it works beautifully. I have not tested with all scenarios but I have faith it should work. We try to find a complicated solution to a simple problem specially when stressed out.
Thanks again I truly appreciate all your help,
Nat
You are not authorized to post a reply.

Acceptable Use Policy