Author |
Topic |
KimHJ
Starting Member
4 Posts |
Posted - 2014-07-10 : 19:34:42
|
I have the following tables.Employee tableEmpNum, Department, Gender100 AAA M101 AAA F102 BBB M103 BBB F104 AAA M105 BBB F EmpProducts tableEmpNum,Item100 A1100 A1100 A2101 A2102 B1102 B3103 B2104 A1104 A2105 B1Products tableDeparment, Item, QtyM, QtyFAAA A1 2 1AAA A2 1 0AAA A3 1 1BBB B1 1 1BBB B2 2 3BBB 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 -2102 B3 -2103 B1 -1103 B2 -2103 B2 -3104 A1 -1104 A3 -1105 B2 -3105 B3 -3I made this SQL but i got stock in the count.Select ep.item, e.Empnum, p.item from employee eINNER JOIN products p on p.department = e.departmentLEFT 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 femalewhere EmpProductCount <> ProductsQtyorder by EmpnumThanks 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 -2Why it has that value |
|
|
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 -2Why 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-16 : 19:04:59
|
http://www.sqlservercentral.com/articles/Best+Practices/61537/Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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? |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
|