Retrieve Column Name of First Column Where Value Less Than... 1

[Fletch12]

Unfortunately I have a poorly designed table that I need to query. The sample data and desired query result are listed below.

I need to write a query that will result in the first column name that's found where the value is less than 20,000 (for example) for that item. If it's not possible to return the name of the column, a count of the columns found that are less than 20,000 would also work. I've been able to do this in Excel by using the "MATCH" and "COUNTIF" functions combined, but I'm struggling with how to do this in SQL. Thanks for any help.

Table1
Item,Day0,Day1,Day2,Day3,Day4,Day5

"ABC1","8,192","15,328","22,528","29,536","36,672","36,672"
"ABC2","500","1,328","15,000","19,536","26,672","36,672"
"ABC3","192","328","18,000","29,536","36,672","36,672"

Desired Result
Item,Column

"ABC1","Day1"
"ABC2","Day3"
"ABC3","Day2

 

[gmmastros]

Try this. If it works and you would like me to explain it, let me know.

Select Item,
       Coalesce(Case When Day0 >= 20000 Then 'Day0' End,
                Case When Day1 >= 20000 Then 'Day1' End,
                Case When Day2 >= 20000 Then 'Day2' End,
                'There are none')
From   YourTableName

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Hey George,

I tried your example and did not get the same result.

I got it with....

with cte as (
select Item, day0 as myamt, 'DAY0' as txtday
from Table99
union all
select Item, day1 as myamt, 'DAY1' as txtday
from Table99
union all
select Item, day2 as myamt, 'DAY2' as txtday
from Table99
union all
select Item, day3 as myamt, 'DAY3' as txtday
from Table99
union all
select Item, day4 as myamt, 'DAY4' as txtday
from Table99
union all
select Item, day5 as myamt, 'DAY5' as txtday
from Table99
),
BYRANK as (
Select row_number() over (partition by item
order by myamt desc) as MyRank, *
from CTE
where myamt < 20000
)
Select *
from byrank
where MyRank=1

Simi

 

[gmmastros]

Thanks for keeping me on my toes Simi.

Here's a query that is sure to work:

Select Item,
       Case When Day5 < 20000 Then 'Day5'
            When Day4 < 20000 Then 'Day4'
            When Day3 < 20000 Then 'Day3'
            When Day2 < 20000 Then 'Day2'
            When Day1 < 20000 Then 'Day1'
            When Day0 < 20000 Then 'Day0'
            End
From   YourTableNameHere

I tested like this:

Declare @Temp Table(Item VarChar(20),Day0 Int,Day1 Int,Day2 Int,Day3 int,Day4 int,Day5 int)

Insert Into @Temp Values('ABC1',8192,15328,22528,29536,36672,36672)
Insert Into @Temp Values('ABC2',500,1328,15000,19536,26672,36672)
Insert Into @Temp Values('ABC3',192,328,18000,29536,36672,36672)

Select Item,
       Case When Day5 < 20000 Then 'Day5'
            When Day4 < 20000 Then 'Day4'
            When Day3 < 20000 Then 'Day3'
            When Day2 < 20000 Then 'Day2'
            When Day1 < 20000 Then 'Day1'
            When Day0 < 20000 Then 'Day0'
            End
From @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Yes, yours is more elagent, but I should get points for using a double cte and a ranking function....

lol

: )

Simi

 

[Fletch12]

This works, but it's more complicated that I originally posted. The 20,000 value was just an example. This value can be different for each item, and is derived from another field in a different table. So I need to be able to loop through Table2, and for each item in Table2 return the MAXDAYS value to be used in this query. The query below works but only for part ABC1.

Select Item,
	Case    When Day5 < (SELECT MAXDAYS FROM TABLE2 WHERE ITEM = 'ABC1') Then 'Day5'
		When Day4 < (SELECT MAXDAYS FROM TABLE2 WHERE ITEM = 'ABC1') Then 'Day4'
		When Day3 < (SELECT MAXDAYS FROM TABLE2 WHERE ITEM = 'ABC1') Then 'Day3'
		When Day2 < (SELECT MAXDAYS FROM TABLE2 WHERE ITEM = 'ABC1') Then 'Day2'
		When Day1 < (SELECT MAXDAYS FROM TABLE2 WHERE ITEM = 'ABC1') Then 'Day1'
		When Day0 < (SELECT MAXDAYS FROM TABLE2 WHERE ITEM = 'ABC1') Then 'Day0'
		End
From Table1

 

[markros]

Why you can not JOIN with this table2?

select T1.Item, case when T1.Day5 < T2.MaxDays then 'Day5'
                      ....
from Table1 T1 inner join Table2 T2 on T1.Item = T2.Item

PluralSight Learning Library

 

[Fletch12]

I was able to get this working. Thanks for the help!