quote:

---

Originally posted by Deon Smit

I have got 2 tables. SKU and SKUXLOC. SKUXLOC is the details for SKU. Locations where stuff is putt. Here is my script.

Select * from SKU C
Join (Select SKU from SKUXLOC where LOCATIONTYPE = 'CASE' and QTY > 0) A
On C.SKU = A.SKU
Join (Select SKU from SKUXLOC where LOCATIONTYPE = 'PICK' and QTY = 0) B
On a.sku = b.sku


Table SKU is

SKU | Supp |
-----------------
1234 | Supp1 |
456 | Supp2 |
789 | Supp3 |

Table SKUXLOC is

SKU | SKUXLOC | QTY |
----------------------------------
1234 | PICK | 1 |
1234 | Case | 0 |
1234 | CASE | 55 |
1234 | PICK | 0 |
1234 | CHER | 71 |
456 | CASE | 88 |
456 | PICK | 0 |
456 | PICK | 0 |
456 | CASE | 7 |
456 | PICKTO | 9 |
789 | PICK | 10 |
789 | CASE | 5 |

My Query gives me SKU 1234 and 456. Because it has a line with PICK and QTY = 0 and a line with CASE and QTY > 0 Per SKU. I need the SUM op the qty Of CASE and PICK first to be calculated. And then my conditions should be met. In that case only SKU 456 should return. Cause Total PICK = 0 And Total Case = 95. Not 1234 Cause PICK = 1 and Case= 55. So only one condition is met.

Can any one help me.

Regards.

---

SELECT SKU.*
FROM SKU main
INNER JOIN
(
SELECT SKU,
 SUM(CASE WHEN SKUXLOC='PICK' THEN QTY ELSE 0 END) AS Pick,
SUM(CASE WHEN SKUXLOC='CASE' THEN QTY ELSE 0 END) AS Case
FROM SKUXLOC
GROUP BY SKU)tmp
ON tmp.SKU=main.SKU
WHERE tmp.Pick=0 AND tmp.Case>0

quote:

---

Originally posted by metaraja

can try like this

Select * from SKU C
Join (Select SKU, sum(QTY) as qty from SKUXLOC where LOCATIONTYPE = 'CASE' group by SKU ,LOCATIONTYPE ) A
On (C.SKU = A.SKU and a.QTY > 0 )
Join (Select SKU,sum(QTY) as qty from SKUXLOC where LOCATIONTYPE = 'PICK' group by SKU ,LOCATIONTYPE ) B
On ( a.sku = b.sku and b.QTY = 0 )

Raja

---