Case When issue

Question

Case When issue

d13rosew

SSC Rookie

Points: 34
More actions
August 16, 2017 at 1:59 pm

#339323

Hello, I'm still new to SQL and i'm stuck. I have a case when statement but the results aren't coming out how I want. Below is my code and results. The issue is all with the rows with the same UPPERAGE should be combine not sure what I need to do for that. Thank you all for any help

Select UPPERAGE,
case when [DurationLower] <= 2 THEN [DurationLower] ELSE NULL END AS 'First trail year',
case when [DurationLower] >= 3 THEN [DurationLower] ELSE NULL END AS 'Next trail year',
case when [DurationLower] <= 2 THEN Rate/100 ELSE NULL END AS 'First trail %',
case when [DurationLower] >= 3 THEN Rate/100 ELSE NULL END AS 'Next trail %'
From Pic.PageToProduct_VW pp
left join Pic.PageToProductToRate_vw ppr on ppr.PageToProductID= pp.PageToProductID
left join Pic.ProductRates_VW r on ppr.ProductRatesID= r.ProductRatesID
wHERE pp.PageID = '76A1'
and r.RateType = 'BA POINTS'
Results - what i'd like is to see only two rows in the results.... I'm sure it's a simple fix but it's beyond by skills right now.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

d13rosew - Wednesday, August 16, 2017 1:59 PM
Hello, I'm still new to SQL and i'm stuck. I have a case when statement but the results aren't coming out how I want. Below is my code and results. The issue is all with the rows with the same UPPERAGE should be combine not sure what I need to do for that. Thank you all for any help

Select UPPERAGE,
case when [DurationLower] <= 2 THEN [DurationLower] ELSE NULL END AS 'First trail year',
case when [DurationLower] >= 3 THEN [DurationLower] ELSE NULL END AS 'Next trail year',
case when [DurationLower] <= 2 THEN Rate/100 ELSE NULL END AS 'First trail %',
case when [DurationLower] >= 3 THEN Rate/100 ELSE NULL END AS 'Next trail %'
From Pic.PageToProduct_VW pp
left join Pic.PageToProductToRate_vw ppr on ppr.PageToProductID= pp.PageToProductID
left join Pic.ProductRates_VW r on ppr.ProductRatesID= r.ProductRatesID
wHERE pp.PageID = '76A1'
and r.RateType = 'BA POINTS'
Results - what i'd like is to see only two rows in the results.... I'm sure it's a simple fix but it's beyond by skills right now.

Please show us which two rows you want to see.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

d13rosew SSC Rookie Points: 34 More actions · Answer 2

Phil Parkin - Wednesday, August 16, 2017 2:10 PM
d13rosew - Wednesday, August 16, 2017 1:59 PM
Hello, I'm still new to SQL and i'm stuck. I have a case when statement but the results aren't coming out how I want. Below is my code and results. The issue is all with the rows with the same UPPERAGE should be combine not sure what I need to do for that. Thank you all for any help

/Select UPPERAGE,
case when [DurationLower] <= 2 THEN [DurationLower] ELSE NULL END AS 'First trail year',
case when [DurationLower] >= 3 THEN [DurationLower] ELSE NULL END AS 'Next trail year',
case when [DurationLower] <= 2 THEN Rate/100 ELSE NULL END AS 'First trail %',
case when [DurationLower] >= 3 THEN Rate/100 ELSE NULL END AS 'Next trail %'
From Pic.PageToProduct_VW pp
left join Pic.PageToProductToRate_vw ppr on ppr.PageToProductID= pp.PageToProductID
left join Pic.ProductRates_VW r on ppr.ProductRatesID= r.ProductRatesID
wHERE pp.PageID = '76A1'
and r.RateType = 'BA POINTS'/
Results - what i'd like is to see only two rows in the results.... I'm sure it's a simple fix but it's beyond by skills right now.

Please show us which two rows you want to see.

I want to see rows 1 & 2 combine and rows 3 & 4 combine.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

Something like this should get you most of the way there:

WITH Results AS ( SELECT UPPERAGE , [First trail year] = CASE WHEN DurationLower <= 2 THEN DurationLower ELSE NULL END , [Next trail year] = CASE WHEN DurationLower >= 3 THEN DurationLower ELSE NULL END , [First trail %] = CASE WHEN DurationLower <= 2 THEN Rate / 100 ELSE NULL END , [Next trail %] = CASE WHEN DurationLower >= 3 THEN Rate / 100 ELSE NULL END FROM Pic.PageToProduct_VW pp LEFT JOIN Pic.PageToProductToRate_vw ppr ON ppr.PageToProductID = pp.PageToProductID LEFT JOIN Pic.ProductRates_VW r ON ppr.ProductRatesID = r.ProductRatesID WHERE pp.PageID = '76A1' ) SELECT UPPERAGE , MAX(Results.[First trail year]) , MAX(Results.[Next trail year]) , MAX(Results.[First trail %]) , MAX(Results.[Next trail %]) FROM Results GROUP BY UPPERAGE;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

d13rosew SSC Rookie Points: 34 More actions · Answer 4

Phil Parkin - Wednesday, August 16, 2017 2:24 PM
Something like this should get you most of the way there:
WITH Results AS ( SELECT UPPERAGE , [First trail year] = CASE WHEN DurationLower <= 2 THEN DurationLower ELSE NULL END , [Next trail year] = CASE WHEN DurationLower >= 3 THEN DurationLower ELSE NULL END , [First trail %] = CASE WHEN DurationLower <= 2 THEN Rate / 100 ELSE NULL END , [Next trail %] = CASE WHEN DurationLower >= 3 THEN Rate / 100 ELSE NULL END FROM Pic.PageToProduct_VW pp LEFT JOIN Pic.PageToProductToRate_vw ppr ON ppr.PageToProductID = pp.PageToProductID LEFT JOIN Pic.ProductRates_VW r ON ppr.ProductRatesID = r.ProductRatesID WHERE pp.PageID = '76A1' ) SELECT UPPERAGE , MAX(Results.[First trail year]) , MAX(Results.[Next trail year]) , MAX(Results.[First trail %]) , MAX(Results.[Next trail %]) FROM Results GROUP BY UPPERAGE;

thank you!! I'll give this a go

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 5

d13rosew - Wednesday, August 16, 2017 1:59 PM
Your first problem is that case is not a statement in SQL; itâ€™s an expression. You are starting off the wrong mindset, and your locked back in your old procedural coding days. For example, you put blanksin the aliases because your doing display formatting in the database layer. We donâ€™t do that in SQL; we have a presentation layer to which we pass query results in it does all that stuff with the spacing and fancy type in all that. You basically still writing COBOL. And violating some ISO rules about data elements in mixed systems.
Without DDL, we got start guessing a lot of stuff. This is why itâ€™s been required for over 30 years on SQL forms that you post it. I also notice that youâ€™re trying to get some kind of rate, but might be doing integer arithmetic. thereâ€™s no such thing as a general,universal, magical â€œrateâ€; it has to be the rate of something in particular. The use of the â€œ_VWâ€ affix is called a â€œVolkswagenâ€in data modeling slang, and we donâ€™t like it. A view is just as much a table as a base table. There is also no such thing and a validdata model as a â€œsomething_rate_idâ€ or â€œsomething_rate_typeâ€. Those postfixes are called attribute properties and an attribute can only have one of them. Read anything from the metadata committee or any book on data modeling.
Butperhaps the biggest problem is you donâ€™t know the differencebetween an attribute and a value, so your data models wrong. Beingthe first trial is not an attribute; being first is a value. Dittofor all values that follow. This is why we wanted DDL
Outerjoins are rare in a properly designed schema. Theyâ€™re notautomatically wrong, just rare. The reason is that in a well designedschema, the tables are connected by references so thereâ€™s always amatch and we designed our model to favor inner joins.
I don't know if you can fix your schema at this point. But I do remember the Turkish proverb â€œno matter how far you have gone down the wrong road, turn aroundâ€

Please post DDL and follow ANSI/ISO standards when asking for help.