Combining data from two tables

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining data from two tables

Author

Topic

jay_tee_1999
Starting Member

2 Posts

Posted - 2008-04-29 : 15:22:09

Hello all - I'm relatively new to SQL and has been struggling with the below query. Any help is greatly appreciated. Thanks.

I have the following 2 tables:

I'm trying to get the below output:

Here's the SQL I've been using:
SELECT
a.PERSON_ID,
sum(a.AMOUNT),
CASE
WHEN b.JOB_CODE = 10 then "Level 2 Seller"
WHEN b.JOB_CODE = 15 then "Level 3 Seller"
ELSE
'Level 1 Seller'
END as "Job_Code"
FROM
tbl_SALES a,
tbl_JOBFUNCTION b
WHERE
a.PERSON_ID = b.PERSON_ID
GROUP BY
a.PERSON_ID,
b.JOB_CODE

Here's what my results are with the query I've been using:

Each person could have many job codes, but when they have 10 then they're consider "level 2".
- If they have 15 then they're "level 3".
- If they don't have either 10 or 15 then they're "level 1".
- If they have both 10 and 15 they're still "level 3".

Please help.

Thanks,
Jim

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-29 : 15:35:30

try:


select
	s.person_id
	,s.amount
	,(case when max(j.job_code) = 10 then 'Level 2'
		when max(j.job_code) = 15 then 'Level 3'
		else 'Level 1' end) as Job_Function
from
		tbl_sales as s
		join tbl_jobfunction as j
		on s.person_id = j.person_id

group by s.person_id, s.amount

jay_tee_1999
Starting Member

2 Posts

Posted - 2008-04-29 : 16:16:32

quote:

Originally posted by slimt_slimt

try:


select
	s.person_id
	,s.amount
	,(case when max(j.job_code) = 10 then 'Level 2'
		when max(j.job_code) = 15 then 'Level 3'
		else 'Level 1' end) as Job_Function
from
		tbl_sales as s
		join tbl_jobfunction as j
		on s.person_id = j.person_id

group by s.person_id, s.amount

Thanks much slimt. It got me a lot closer to where I needed to be.
However, I made a mistake with one of my scenarios.
Instead of
- If they have both 10 and 15 they're still "level 3".
it should be
- If they have both 10 and 15 then it'll be "level 2".

How would I address this dual code thing? Thanks again!!

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-29 : 16:35:45

try; or correct the sum in case of other scenario.


select
	s.person_id
	,s.amount
	,(case when sum(joblevel) < 10 then 'level1'
			when sum(joblevel) between 15 and 22 then 'level3'
			else 'level2' end)as Job_Function
from
		tbl_sales as s
		join tbl_jobfunction as j
		on s.person_id = j.person_id

group by s.person_id, s.amount

if job_function 1 or 2 or 3 then level1
if job_function (1 or 2 or 3 or) 15 then level3
if job_function 10 or 15 then level2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources