complex query

Last Post 11 Apr 2006 01:07 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

--
11 Apr 2006 12:15 PM
Here is the table structure:
create table #temp1
(Deptid int,
EmpCode varchar(10),
CodeDesc varchar(50),
EmpSal int,
EmpSpendlmt int)
insert #temp1
Values
(1,'e1','exempt',75000,1000)
insert #temp1
Values
(1, 'e2','exempt', 85000,500)
insert #temp1
Values
(1,'N', 'nonexempt',25000,0)
insert #temp1
Values
(1,'s1','supervisor(grp1)',10000,5000)
insert #temp1
Values
(1,'s2','Supervisor(grp2)','10000',5000)
insert #temp1
Values
(2,'mgr','Manager',15000,7500)
insert #temp1
Values
(2,'vp','vicepresident',250000,10000)
insert #temp1
Values
(2,'P1','President(op)',500000,100000)
insert #temp1
Values
(2,'p2','President(Tech)',500000,200000)
insert #temp1
Values
(2,'S','supervisor',25000,5000)
insert #temp1
Values
(2,'E','exempt',70000,1500)

--select * from #temp1
DeptID EmpCode EmpDesc EmpSal EmpSpendlmt
1 e1 exempt 75000 1000
1 e2 exempt 85000 500
1 N nonexempt 25000 0
1 s1 supervisor(grp1) 10000 5000
1 s2 Supervisor(grp2) 10000 5000
2 mgr Manager 15000 7500
2 vp vicepresident 250000 10000
2 P1 President(op) 500000 100000
2 p2 President(Tech) 500000 200000
2 S supervisor 25000 5000
2 E exempt 70000 1500

Here is the code:

select DeptID,
max(case when substring(empcode,1,1) = 'E'
then rtrim(empcode)
else null end) as exemptcode,
max(case when substring(empcode,1,1)= 'E'
then codedesc else null end) as ExemptDesc,
max(case when substring(empcode,1,1) = 'E'
then empsal
else null end) as exemptsal, -- This should be combined
max(case when substring(empcode,1,1)= 'E'
then empspendlmt else null end) as Exemptspendlmt,
max(case when empcode = 'N'
then empcode
else null end) as nonexemptcode,
max(case when empcode= 'N'
then codedesc else null end) as nonExemptDesc,
max(case when empcode = 'N'
then empsal
else null end) as nonexemptsal,
max(case when empcode= 'N'
then empspendlmt else null end) as nonExemptspendlmt,
max(case when substring(empcode,1,1) = 'S'
then empcode
else null end) as SupervisorCode,
max(case when substring(empcode,1,1) = 'S'
then codedesc else null end) as supervisorDesc,
max(case when substring(empcode,1,1) = 'S'
then empsal
else null end) as Supervisorsal, -- This should be combined
max(case when substring(empcode,1,1) = 'S'
then empspendlmt else null end) as supervisorspendlmt,
max(case when empcode = 'MGR'
then empcode
else null end) as Managercode,
max(case when empcode= 'MGR'
then codedesc else null end) as ManagerDesc,
max(case when empcode = 'MGR'
then empsal
else null end) as Managersal,
max(case when empcode= 'MGR'
then empspendlmt else null end) as Managerspendlmt,
max(case when empcode = 'VP'
then Rtrim(empcode)
else null end) as VIcepresidentCode,
max(case when empcode = 'VP'
then codedesc else null end) as VicepresidentDesc,
max(case when empcode = 'VP'
then empsal
else null end) as VIcepresidentsal,
max(case when empcode = 'VP'
then empspendlmt else null end) as Vicepresidentspendlmt,
max(case when substring(empcode,1,1) = 'P'
then rtrim(empcode)
else null end) as Presidentcode,
max(case when substring(empcode,1,1) = 'P'
then codedesc else null end) as PresidentDesc,
max(case when substring(empcode,1,1) = 'P'
then empsal
else null end) as Presidentsal, -- This should be combined
max(case when substring(empcode,1,1) = 'P'
then empspend
nosepicker
New Member
New Member

--
11 Apr 2006 01:07 PM
To combine the salaries and spending limits, this should work:

select DeptID,
max(case when substring(empcode,1,1) = 'E' then rtrim(empcode) else null end) as exemptcode,
max(case when substring(empcode,1,1)= 'E' then codedesc else null end) as ExemptDesc,
sum(case when substring(empcode,1,1) = 'E' then empsal else 0 end) as exemptsal, -- This should be combined
sum(case when substring(empcode,1,1)= 'E' then empspendlmt else 0 end) as Exemptspendlmt,
max(case when empcode = 'N' then empcode else null end) as nonexemptcode,
max(case when empcode= 'N' then codedesc else null end) as nonExemptDesc,
sum(case when empcode = 'N' then empsal else 0 end) as nonexemptsal,
sum(case when empcode= 'N' then empspendlmt else 0 end) as nonExemptspendlmt,
max(case when substring(empcode,1,1) = 'S' then empcode else null end) as SupervisorCode,
max(case when substring(empcode,1,1) = 'S' then codedesc else null end) as supervisorDesc,
sum(case when substring(empcode,1,1) = 'S' then empsal else 0 end) as Supervisorsal, -- This should be combined
sum(case when substring(empcode,1,1) = 'S' then empspendlmt else 0 end) as supervisorspendlmt,
max(case when empcode = 'MGR' then empcode else null end) as Managercode,
max(case when empcode= 'MGR' then codedesc else null end) as ManagerDesc,
sum(case when empcode = 'MGR' then empsal else 0 end) as Managersal,
sum(case when empcode= 'MGR' then empspendlmt else 0 end) as Managerspendlmt,
max(case when empcode = 'VP' then Rtrim(empcode) else null end) as VIcepresidentCode,
max(case when empcode = 'VP' then codedesc else null end) as VicepresidentDesc,
sum(case when empcode = 'VP' then empsal else 0 end) as VIcepresidentsal,
sum(case when empcode = 'VP' then empspendlmt else 0 end) as Vicepresidentspendlmt,
max(case when substring(empcode,1,1) = 'P' then rtrim(empcode) else null end) as Presidentcode,
max(case when substring(empcode,1,1) = 'P' then codedesc else null end) as PresidentDesc,
sum(case when substring(empcode,1,1) = 'P' then empsal else 0 end) as Presidentsal, -- This should be combined
sum(case when substring(empcode,1,1) = 'P' then empspendlmt else 0 end) as Presidentspendlmt
from #temp1
group by deptID

For the number of codes issue, I have a question. If there are empcodes like E1, E2, E3, E4, E5 ..., and they all have a description of "exempt", they should all be combined together, yes? If so, then this query doesn't need to be changed. If however there are additional code descriptions that could be added here, then that would be a bigger issue. You can use dynamic SQL for this, but it would be a little involved. These kinds of things are better handled by front-end applications. SQL is not well designed for displaying records in different formats.
natasha
New Member
New Member

--
11 Apr 2006 04:42 PM
Thank you for your help. Actually after posting and playing around more with the code I had a Sum(case...) in place. Yes, your assumption is correct there could be addtional code description and empcodes.
Using dynamic is out of question. For security reasons we are not allowed to use dynamic SQL on our codes on production servers.
I, actually have a list of possible codes that can be used in the table. So at the most, the temp table I am populating with flattened data will have bunch of columns with null values, unless someone out there has any other bright ideas.
Actually this code is not going to be use for display. It is going to be used for an on going migration/data loading. Trying to combine a record with different codes and populating it in another existing table. More like denormalizing to certain extent.
Thanks a lot again for your help and attempt. I appreciate it.
Nat
You are not authorized to post a reply.

Acceptable Use Policy