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
 Compare Count left join

Author  Topic 

KimHJ
Starting Member

4 Posts

Posted - 2014-07-10 : 19:34:42
I have the following tables.

Employee table
EmpNum, Department, Gender
100 AAA M
101 AAA F
102 BBB M
103 BBB F
104 AAA M
105 BBB F

EmpProducts table
EmpNum,Item
100 A1
100 A1
100 A2
101 A2
102 B1
102 B3
103 B2
104 A1
104 A2
105 B1


Products table
Deparment, Item, QtyM, QtyF
AAA A1 2 1
AAA A2 1 0
AAA A3 1 1
BBB B1 1 1
BBB B2 2 3
BBB B3 3 3

Each employee need to have a specific amount of product that is assigned to the employees department I need to know if they have to little or to many. The result should be like this.

100 have the correct items for the gender and department so he should not be in the result.

101 A2 1 She is 1 over the 0 she should have.
102 B2 -2
102 B3 -2
103 B1 -1
103 B2 -2
103 B2 -3
104 A1 -1
104 A3 -1
105 B2 -3
105 B3 -3

I made this SQL but i got stock in the count.

Select ep.item, e.Empnum, p.item from employee e
INNER JOIN products p
on p.department = e.department
LEFT JOIN EmpProducts ep
on e.Empnum = ep.EmpNum
and p.items = ep.item

//here I need the count of the specefic item and compare it against the QtyM if gender is male and against QtyF if gender is female

where
EmpProductCount <> ProductsQty
order by Empnum

Thanks for any help.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2014-07-11 : 18:25:56
Could you elaborate the logic.

On the expected output,

102 B2 -2

Why it has that value
Go to Top of Page

KimHJ
Starting Member

4 Posts

Posted - 2014-07-16 : 18:43:57
quote:
Originally posted by namman

Could you elaborate the logic.

On the expected output,

102 B2 -2

Why it has that value



The Employee 102 working in Department BBB is suppose to have 2 of the products B2 because employee 102 is a Male. Employee 102 do not have any B2 so the employee is in -2
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-16 : 19:04:59
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-16 : 19:25:51
I looked at this the other day and wrote a solution, but 100 was in my output needing an A3.

You said 100 is not in the output because they have all they are supposed to. However, Employee 100 is an employee in AAA. and should have 2 A1's, 1 A2 and 1 A3. You have him with 2 A1's and an A2 and say he shouldn't be in the output. Why shouldn't he be in the list needing an A3?
Go to Top of Page

KimHJ
Starting Member

4 Posts

Posted - 2014-07-22 : 16:54:01
quote:
Originally posted by MichaelJSQL

I looked at this the other day and wrote a solution, but 100 was in my output needing an A3.

You said 100 is not in the output because they have all they are supposed to. However, Employee 100 is an employee in AAA. and should have 2 A1's, 1 A2 and 1 A3. You have him with 2 A1's and an A2 and say he shouldn't be in the output. Why shouldn't he be in the list needing an A3?



You are right he is missing one A3, sorry. I used brain power and not computer to get the result.
I would like to know your solution, thanks.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-22 : 19:37:27
Try this:
select e.EmpNum
,p.Item
,count(ep.Item)-avg(case when e.Gender='M' then p.QtyM else p.QtyF end) as c
from Products as p
inner join Employee as e
on e.Department=p.Deparment
left outer join EmpProducts as ep
on ep.EmpNum=e.EmpNum
and ep.Item=p.Item
group by e.EmpNum
,p.Item
having count(ep.Item)<>avg(case when e.Gender='M' then p.QtyM else p.QtyF end)
order by e.EmpNum
,p.Item
Go to Top of Page

KimHJ
Starting Member

4 Posts

Posted - 2014-07-22 : 20:55:07
quote:
Originally posted by bitsmed

Try this:
select e.EmpNum
,p.Item
,count(ep.Item)-avg(case when e.Gender='M' then p.QtyM else p.QtyF end) as c
from Products as p
inner join Employee as e
on e.Department=p.Deparment
left outer join EmpProducts as ep
on ep.EmpNum=e.EmpNum
and ep.Item=p.Item
group by e.EmpNum
,p.Item
having count(ep.Item)<>avg(case when e.Gender='M' then p.QtyM else p.QtyF end)
order by e.EmpNum
,p.Item




Thanks you made my day.
Go to Top of Page
   

- Advertisement -