May 1, 2013 at 7:33 am
I have this code working fine. I need to subtract one column from another to get the corrected results. I tried qty-NewQty and the results were incorrect. Any ideas?
select s.partNo, p.descript,
sum(case when battState = '99' then qty else '0'end) as CLNT,
sum(case when battState = '1' then qty else '0' end) as AVAIL,
sum(case when battState = '20' then qty else '0' end) as QAH,
sum(case when battState = '30' then qty else '0' end) as BOOST,
sum(case when battState = '31' then qty 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
May 1, 2013 at 7:38 am
Where's [NewQty]?
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
May 1, 2013 at 7:43 am
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
May 1, 2013 at 7:46 am
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
_______________________________________________________________
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/
May 1, 2013 at 7:46 am
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
May 1, 2013 at 7:48 am
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
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
May 1, 2013 at 8:07 am
Got it! Thanks. I found the issue, it was in my test value. My code reply is correct.
May 1, 2013 at 8:08 am
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/
May 1, 2013 at 8:11 am
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. 😀
Yeah I know - cheap hey!!
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
May 1, 2013 at 9:08 am
newQty is in the same table. The application will update the value.
May 2, 2013 at 9:37 am
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.
Jason Wolfkill
May 3, 2013 at 2:40 am
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 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
May 3, 2013 at 5:23 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply