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. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/