Sean Lange (5/1/2013)

---

ChrisM@Work (5/1/2013)

---

Sean Lange (5/1/2013)

---

So what exactly is the issue here?

I would suggest you use the newer join constructs. With this query you don't even need a where clause.

Here is the syntax:

from batt_State s

join pnLU as p on s.PartNo = p.PartNo

group by s.partNo, p.descript

I'd change that to:

SELECT s.partNo, p.descript,

CLNT= SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END),

AVAIL= SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END),

QAH= SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END),

BOOST= SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END),

RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END)

FROM batt_State s

INNER JOIN pnLU p

ON s.PartNo = p.PartNo

WHERE battState IN ('1','20','30','31','99')

GROUP BY s.partNo, p.descript

Oh sure...that will only be better if you want the query to be faster. 😀