Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SUM CASE, COLUMN MINUS COLUMN Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
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

Post #1448412
Posted Wednesday, May 1, 2013 7:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
Where's [NewQty]?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1448416
Posted Wednesday, May 1, 2013 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
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

Post #1448419
Posted Wednesday, May 1, 2013 7:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448421
Posted Wednesday, May 1, 2013 7:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1448422
Posted Wednesday, May 1, 2013 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1448423
Posted Wednesday, May 1, 2013 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
Got it! Thanks. I found the issue, it was in my test value. My code reply is correct.
Post #1448430
Posted Wednesday, May 1, 2013 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448431
Posted Wednesday, May 1, 2013 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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!!


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1448432
Posted Wednesday, May 1, 2013 9:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:31 AM
Points: 100, Visits: 332
newQty is in the same table. The application will update the value.
Post #1448463
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse