kabaari (5/1/2013)
select s.partNo, p.descript,sum(case when battState = '99' then qty else '0'end) as CLNT,
sum(case when battState = '1' then qty-newQty else '0' end) as AVAIL,
sum(case when battState = '20' then qty-newQty else '0' end) as QAH,
sum(case when battState = '30' then qty-newQty else '0' end) as BOOST,
sum(case when battState = '31' then qty-newQty else '0' end) as RESTING
from batt_State s, pnLU as p
where 0=0 and s.PartNo = p.PartNo
group by s.partNo, p.descript
Which table is [newQty] from? In what way are the results incorrect? We can't see what you see - it's a bit of a guessing game.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden