SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Compare Count left join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KimHJ
Starting Member

USA
4 Posts

Posted - 07/10/2014 :  19:34:42  Show Profile  Reply with Quote
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

USA
272 Posts

Posted - 07/11/2014 :  18:25:56  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 07/16/2014 :  18:43:57  Show Profile  Reply with Quote
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

USA
37167 Posts

Posted - 07/16/2014 :  19:04:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Posting Yak Master

172 Posts

Posted - 07/16/2014 :  19:25:51  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 07/22/2014 :  16:54:01  Show Profile  Reply with Quote
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.

Edited by - KimHJ on 07/22/2014 16:59:42
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

415 Posts

Posted - 07/22/2014 :  19:37:27  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 07/22/2014 :  20:55:07  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000