Select Query Issue

Question

Select Query Issue

catherine

SSC Rookie

Points: 35
More actions
June 9, 2011 at 4:49 am

#252469

I have a table (table PS) with 20 fields e.g. currentvalue1, currentvalue2 ...etc These fields are decimal(8,2) Null. I want to get the max value i.e. the last currentvalue with a non 0 value/null held. I want this added to my current select statement
e.g. select name, age, sex ....etc where id = @id
Any ideas

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: 246960 More actions · Answer 1

The last current value with a non 0 value/null held

I've tried, but I don't know what that means. Is there a date in there from which to determine 'last'? Is there a history indicator to indicate 'current'?

Please provide some sample data & desired result to make this clearer.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

catherine SSC Rookie Points: 35 More actions · Answer 2

CurrentValue1 = 39340.00

CurrentVaule2 = 49340.00

CurrentValue3 = 29370.00

CurrentValue4 = 0 or Null

CurrentValue5 = 0 or Null

....etc

therefore the max point would be the value from CurrentValue3

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

catherine (6/9/2011)
CurrentValue1 = 39340.00
CurrentVaule2 = 49340.00
CurrentValue3 = 29370.00
CurrentValue4 = 0 or Null
CurrentValue5 = 0 or Null
....etc
therefore the max point would be the value from CurrentValue3

select Coalesce(NullIf(CurVal5,0), NullIf(CurVal4,0), NullIf(CurVal3,0), NullIf(CurVal2,0), NullIf(CurVal1,0))

should return the last non-Null and non-zero entry in the series.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

catherine (6/9/2011)
I have a table (table PS) with 20 fields e.g. currentvalue1, currentvalue2 ...etc These fields are decimal(8,2) Null. I want to get the max value i.e. the last currentvalue with a non 0 value/null held. I want this added to my current select statement
e.g. select name, age, sex ....etc where id = @id
Any ideas

You want the MAX non-null/non-zero value for all 20 "fields" for a given name? If soe, does that include the age/sex columns? Also, from your query, it would appear that each name is associated with an ID. Is that correct?

Last but not least, what actually identifies the order of entry for each of these "fields" temporally? This can't be done without such information.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

catherine SSC Rookie Points: 35 More actions · Answer 5

catherine

SSC Rookie

Points: 35

June 9, 2011 at 5:31 am

#1336186

using Coalesce that has solved it

thanks