ChrisM@Work (5/3/2013)
wolfkillj (5/2/2013)
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. 😀
That's also assuming that the business requirement is to return rows only where battState IN ('1','20','30','31','99'), which isn't stated by the OP. If he wants a row for every partNo in the batt_State table, but only wants the aggregate SUM values where battState IN ('1','20','30','31','99') (leaving 0 in these columns for other values of battState), then the WHERE battState IN ('1','20','30','31','99') clause should be omitted.
You're absolutely right, Wolfie - but as an experienced dev, I'd bet that you'd write the same as I did, or possibly both, and offer it to a stakeholder to choose.
For something this short, I probably would just write it and show the stakeholder the results with and without the WHERE clause. Anything more complex, I'd want clarification of the requirements first - I don't like to spend too much time coding after phantom requirements. :hehe:
Jason Wolfkill