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