October 13, 2008 at 3:57 am
Hi,
I have a table that holds the data for Books related to each class subject wise. The structure of the table is
Table Name: SubjectClassPricingMap
Columns:
ClassID BIGINT,
SubjectID BIGINT,
CostOfBook NUMERIC(18,2)
Description TEXT
I was trying to write a stored proc that allows two parameters each for one class. I need to display the following differential report.
SubjectID, CostForClassA, CostForClassB
1 9.99 10.99
2 0.00 10.99
3 8.99 0.00
When I tried using case statements to generate the output, I was getting the output as follows
SubjectID, CostForClassA, CostForClassB
1 9.99 10.99
2 0.0 10.99
2 10.99 0.00
3 8.99 0.00
3 0.00 8.99
Here is the script
SELECTDISTINCT SubjectID,
CASE WHEN ClassID= @FirstClassID
THEN
ISNULL(CostOfBook,0)
ELSE 0.0
END AS FirstClassCost
CASE WHEN ClassID= @SecondClassID
THEN
ISNULL(CostOfBook,0)
ELSE 0.0
END SecondClassCost
FROM
dbo.SubjectClassPricingMap
WHERE
ClassID IN (@FirstClassID, @SecondClassID)
Thanks in advance.
October 13, 2008 at 4:10 am
Hi Ravi
Use a GROUP BY to aggregate the results to SubjectID level:
SELECT SubjectID,
MAX(CASE WHEN ClassID= @FirstClassID THEN ISNULL(CostOfBook,0) ELSE 0.0 END) AS FirstClassCost
MAX(CASE WHEN ClassID= @SecondClassID THEN ISNULL(CostOfBook,0) ELSE 0.0 END) AS SecondClassCost
FROM dbo.SubjectClassPricingMap
WHERE ClassID IN (@FirstClassID, @SecondClassID)
GROUP BY SubjectID
Cheers
ChrisM
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
October 13, 2008 at 4:17 am
Thank you Chris.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply