Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
... and so on).
Just wondering.
Thank you!
Kyra
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
Kyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA
|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:

>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>
|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:

> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
>