SQLTeam.com | Weblogs | Forums

Return MAX values from multiple columns - SQL

Good Evening friends,

I need help with this SQL code. Please see my code below: I am using SQL Database 2019.

DECLARE @sampleTestData TABLE
(
CustID INT PRIMARY KEY
,[Total] numeric(8,2)
,[0-30] numeric(8,2)
,[31-60] numeric(8,2)
,[61-90] numeric(8,2)
,[91-120] numeric(8,2)
,[121-150] numeric(8,2)
,[151-180] numeric(8,2)
,[181-365] numeric(8,2)
,[1-2 Yrs] numeric(8,2)
,[2-3 Yrs] numeric(8,2)
,[3-4 Yrs] numeric(8,2)
,[4+] numeric(8,2)
,AgeBand varchar(50)
)

INSERT INTO @sampleTestData
VALUES ( 3160, 17.38, 0, 0, 0,0,0,0,0,0,0,0,17.38,'' ) ---return 4+ AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 11513, 45.34, 0, 0, 0,0,0,0,0,0,0,0,45.34,'' ) ---return 4+ AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 24713, 170.34, 170.34, 0, 0,333.26,0,0,0,0,0,0 ,0,'') ---Return 0-30 AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 24867, 710.02, 0, 0, 316.41,0,0,333.26,60.35,0,20.5,0,40.50 ,'') ---Return 151-180 AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 456939,378.86,77.61, 0, 0, 77.83, 0, 0, 157.24, 66.18, 0, 0, 0 ,'') ---Return 181-365 AS AgedBand
INSERT INTO @sampleTestData
VALUES (395965,557.97, 77.61, 0, 0, 77.83, 0, 0, 157.24, 245.29, 0, 0, 0,'') ---Return 1-2 yrs AS AgedBand

Select * from @sampleTestData

Basically, I want to return the maximum value as Ageband.
For example, for CustID 3160, I want to return 4+ and for CustID 24867, I want to return 151-180 in the Ageband.

For my desired output, it should be like this:
DECLARE @sampleTestData TABLE
(
CustID INT PRIMARY KEY
,[Total] numeric(8,2)
,[0-30] numeric(8,2)
,[31-60] numeric(8,2)
,[61-90] numeric(8,2)
,[91-120] numeric(8,2)
,[121-150] numeric(8,2)
,[151-180] numeric(8,2)
,[181-365] numeric(8,2)
,[1-2 Yrs] numeric(8,2)
,[2-3 Yrs] numeric(8,2)
,[3-4 Yrs] numeric(8,2)
,[4+] numeric(8,2)
,AgeBand varchar(50)
)

INSERT INTO @sampleTestData
VALUES ( 3160, 17.38, 0, 0, 0,0,0,0,0,0,0,0,17.38,'4+' ) ---return 4+ AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 11513, 45.34, 0, 0, 0,0,0,0,0,0,0,0,45.34,'4+' ) ---return 4+ AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 24713, 170.34, 170.34, 0, 0,333.26,0,0,0,0,0,0 ,0,'0-30') ---Return 0-30 AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 24867, 710.02, 0, 0, 316.41,0,0,333.26,60.35,0,20.5,0,40.50 ,'151-180 ') ---Return 151-180 AS AgedBand

INSERT INTO @sampleTestData
VALUES ( 456939,378.86,77.61, 0, 0, 77.83, 0, 0, 157.24, 66.18, 0, 0, 0 ,'181-365') ---Return 181-365 AS AgedBand
INSERT INTO @sampleTestData
VALUES (395965,557.97, 77.61, 0, 0, 77.83, 0, 0, 157.24, 245.29, 0, 0, 0,'1-2 yrs') ---Return 1-2 yrs AS AgedBand

Select * from @sampleTestData

Thanks in advance.
J

You can use unpivot with a cte. This can also be made dynamic to support more age bands

;with cte as (	select *--custID, AgeBand
		 from 
		 (	Select CustID, [0-30] ,[31-60] ,[61-90] ,[91-120] ,[121-150] ,[151-180] ,[181-365] ,[1-2 Yrs] ,[2-3 Yrs] ,[3-4 Yrs] ,[4+]
			  from #sampleTestData ) as sd
			UNPIVOT 
				(
					AgeBand for Agebands in ( [0-30] ,[31-60] ,[61-90] ,[91-120] ,[121-150] ,[151-180] ,[181-365] ,[1-2 Yrs] ,[2-3 Yrs] ,[3-4 Yrs] ,[4+])
				) as up
		) 

select c.CustID, ct.AgeBands
  from cte ct
	join (Select CustID, max(AgeBand) as Ageband
			from cte
			group by CustID) c
on ct.CustID = c.CustID
and ct.Ageband = c.Ageband
1 Like
SELECT s.*, ca1.AgedBand
FROM @sampleTestData s
CROSS APPLY ( 
    SELECT *, ROW_NUMBER() OVER(ORDER BY value DESC) AS row_num
    FROM ( VALUES
        ([0-30], '0-30'), ([31-60], '31-60'), ([61-90], '61-90'), ([91-120], '91-120'),
        ([121-150], '121-150'), ([151-180], '151-180'), ([181-365], '181-365'), ([1-2 Yrs], '1-2 Yrs'),
        ([2-3 Yrs], '2-3 Yrs'), ([3-4 Yrs], '3-4 Yrs'), ([4+], '4+') 
    ) AS derived(value, AgedBand)
) AS ca1
WHERE ca1.row_num = 1
1 Like

If a tie in high value is possible, do you have preference for lower / higher for ties?

Thank you Scott,

It works like charm. If there is a tie value, I would prefer the highest value. I appreciate your great effort at quick turn around.

Thank you.

Hi Mike, You are genious. Thank you for your help. It works as expected.

I appreciate.

Sample data with tied max values in the same row:

DECLARE @sampleTestData TABLE
(
CustID INT PRIMARY KEY
,[Total] numeric(8,2)
,[0-30] numeric(8,2)
,[31-60] numeric(8,2)
,[61-90] numeric(8,2)
,[91-120] numeric(8,2)
,[121-150] numeric(8,2)
,[151-180] numeric(8,2)
,[181-365] numeric(8,2)
,[1-2 Yrs] numeric(8,2)
,[2-3 Yrs] numeric(8,2)
,[3-4 Yrs] numeric(8,2)
,[4+] numeric(8,2)
,AgeBand varchar(50)
)

INSERT INTO @sampleTestData VALUES 
    ( 3160, 17.38, 0, 0, 0,0,0,0,0,0,0,0,17.38,'' ), ---return 4+ AS AgedBand
    ( 11513, 45.34, 0, 0, 0,0,0,0,0,0,0,0,45.34,'' ), ---return 4+ AS AgedBand
    ( 24713, 170.34, 170.34, 0, 0,333.26,0,0,0,0,0,0 ,0,''), ---Return 0-30 AS AgedBand <<--<< actually 91-120
    ( 24867, 710.02, 0, 0, 316.41,0,0,333.26,60.35,0,20.5,0,40.50 ,''), ---Return 151-180 AS AgedBand
    ( 456939,378.86,77.61, 0, 0, 77.83, 0, 0, 157.24, 66.18, 0, 0, 0 ,''), ---Return 181-365 AS AgedBand
    ( 395965,557.97, 77.61, 0, 0, 77.83, 0, 0, 157.24, 245.29, 0, 0, 0,''), ---Return 1-2 yrs AS AgedBand
    /* add rows with a tie for high value */
    ( 777777, 0, 77.77, 77.77,  0, 0, 0, 0, 0, 0, 0, 0, 0, ''), ---31-60 (higher of tied values)
    ( 888888, 0, 0, 0, 0, 88.88, 0, 0, 0, 88.88, 0, 0, 0 ,'') ---1-2 yrs (higher of tied values)

--Select * from @sampleTestData

SELECT s.*, ca1.AgedBand
FROM @sampleTestData s
CROSS APPLY ( 
    SELECT *, ROW_NUMBER() OVER(ORDER BY value DESC, AgedBand# DESC) AS row_num
    FROM ( VALUES
        ([0-30], '0-30', 1), ([31-60], '31-60', 2), ([61-90], '61-90', 3), ([91-120], '91-120', 4),
        ([121-150], '121-150', 5), ([151-180], '151-180', 6), ([181-365], '181-365', 7), 
        ([1-2 Yrs], '1-2 Yrs', 8), ([2-3 Yrs], '2-3 Yrs', 9), ([3-4 Yrs], '3-4 Yrs', 10), ([4+], '4+', 11) 
    ) AS derived(value, AgedBand, AgedBand#)
) AS ca1
WHERE ca1.row_num = 1
2 Likes