Add value (category Unknown) to Column or Row with highest value per day

Mike_1980 · March 9, 2023, 8:42am

I have data in two tables, same data but different approach, to get the result I'm going for. But then, I'm stuck and I was hoping you could point me in the right direction...

TABLE#1

CREATE TABLE table1
    (Date, Col_A, Col_B, Col_C, Unknown)
;

INSERT INTO table1
    (Date, Col_A, Col_B, Col_C, Unknown)
VALUES
    ('1-3-2023','2','1','5','1'),
    ('2-3-2023','6','0','1','2'),
    ('3-3-2023','0','0','1','1'),
    ('4-3-2023','5','0','2','3')
;

TABLE#2...

CREATE TABLE table2
    (Date, Col_Name, Amount)
;
INSERT INTO table2
    (Date, Col_Name, Amount)
VALUES
    ('1-3-2023','Col_A','2'),
    ('1-3-2023','Col_B','1'),
    ('1-3-2023','Col_C','5'),
    ('1-3-2023','Unknown','1'),
    ('2-3-2023','Col_A','6'),
    ('2-3-2023','Col_B','0'),
    ('2-3-2023','Col_C','1'),
    ('2-3-2023','Unknown','2'),
    ('3-3-2023','Col_A','0'),
    ('3-3-2023','Col_B','0'),
    ('3-3-2023','Col_C','1'),
    ('3-3-2023','Unknown','1'),
    ('4-3-2023','Col_A','5'),
    ('4-3-2023','Col_B','0'),
    ('4-3-2023','Col_C','2'),
    ('4-3-2023','Unknown','3')
;

I've been trying to add the Unknown value to the highest value in Col_A, Col_B or Col_C (per day) in Table#1 or to the highest value where Col_Name is Col_A, Col_B or Col_C (per day) in Table#2.

(select MAX(test) FROM (VALUES (Col_A),(Col_B),(Col_C)) AS value(test)) as Test gave me the highest value per day in Table#1, but I see no way to add this to the column with the highest value in a row.

The desired result is..:

Date      Col_A    Col_B    Col_C
1-3-2023    2        1        6
2-3-2023    8        0        1
3-3-2023    0        0        2
4-3-2023    8        0        2

Many thanks in advance...

Sheriff · March 9, 2023, 9:48am

select t.somedate,
                (case when col_a > col_b and col_a > col_c then
                           col_a + a.Unknown 
                 else col_a end) col_a ,
                 (case when col_b > col_a and col_b > col_c then
                           col_b + a.Unknown 
                 else col_b end) col_b,
                 (case when col_c > col_b and col_a < col_c then
                           col_c + a.Unknown 
                 else col_c end) col_c
from table1 t
cross apply (
          select somedate, unknown from table2 z
          where col_name ='unknown'
          and z.somedate = t.somedate )a

harishgg1 · March 9, 2023, 10:46am

hi

hope this helps

made 2 attempts and deleted my posts .. please excuse .. did not understand

got the results using ONLY Table1

create data script

drop table if exists #table1
CREATE TABLE #table1
(Date1 date, Col_A varchar(1), Col_B int, Col_C int, Unknown int)
INSERT INTO #table1
(Date1, Col_A, Col_B, Col_C, Unknown)
VALUES
('1-3-2023',2,1,5,1),
('2-3-2023',6,0,1,2),
('3-3-2023',0,0,1,1),
('4-3-2023',5,0,2,3)

drop table #table2
CREATE TABLE #table2 (Date1 date, Col_Name varchar(20), Amount int)
INSERT INTO #table2 (Date1, Col_Name, Amount)
VALUES
('1-3-2023','Col_A',2),
('1-3-2023','Col_B',1),
('1-3-2023','Col_C',5),
('1-3-2023','Unknown',1),
('2-3-2023','Col_A',6),
('2-3-2023','Col_B',0),
('2-3-2023','Col_C',1),
('2-3-2023','Unknown',2),
('3-3-2023','Col_A',0),
('3-3-2023','Col_B',0),
('3-3-2023','Col_C',1),
('3-3-2023','Unknown',1),
('4-3-2023','Col_A',5),
('4-3-2023','Col_B',0),
('4-3-2023','Col_C',2),
('4-3-2023','Unknown',3)

select       
     date1
  ,  case when col_a > col_b and col_a > col_c then  col_a + Unknown else col_a end   Col_A                 
  ,  case when col_b > col_a and col_b > col_c then  col_b + Unknown else col_b end   Col_B                 
  ,  case when col_c > col_b and col_c > col_a then  col_c + Unknown else col_c end   Col_C
from 
   #table1 t

Mike_1980 · March 9, 2023, 11:25am

Both solutions seem to be very helpful. It gave me a lot of insight so I want to thank both of you for helping me out..!

harishgg1 · March 9, 2023, 11:59am

hi

hope this helps

i got the result using ONLY Table2 .. earlier i got the results using ONLY Table1

create table2 script

drop table #table2
CREATE TABLE #table2 (Date1 date, Col_Name varchar(20), Amount int)
INSERT INTO #table2 (Date1, Col_Name, Amount)
VALUES
('1-3-2023','Col_A',2),
('1-3-2023','Col_B',1),
('1-3-2023','Col_C',5),
('1-3-2023','Unknown',1),
('2-3-2023','Col_A',6),
('2-3-2023','Col_B',0),
('2-3-2023','Col_C',1),
('2-3-2023','Unknown',2),
('3-3-2023','Col_A',0),
('3-3-2023','Col_B',0),
('3-3-2023','Col_C',1),
('3-3-2023','Unknown',1),
('4-3-2023','Col_A',5),
('4-3-2023','Col_B',0),
('4-3-2023','Col_C',2),
('4-3-2023','Unknown',3)

; with cte as 
(
select       
     date1
      , max( case when Col_Name = 'Col_A'   then amount end ) as Col_A
      , max( case when Col_Name = 'Col_B'   then amount end ) as Col_B
      , max( case when Col_Name = 'Col_C'   then amount end ) as Col_C
      , max( case when Col_Name = 'Unknown' then amount end ) as Unknown
from 
   #table2 
group by 
       date1 
) 
select       
     date1
  ,  case when col_a > col_b and col_a > col_c then  col_a + Unknown else col_a end   Col_A                 
  ,  case when col_b > col_a and col_b > col_c then  col_b + Unknown else col_b end   Col_B                 
  ,  case when col_c > col_b and col_c > col_a then  col_c + Unknown else col_c end   Col_C
from 
   cte

Sheriff · March 9, 2023, 3:14pm

Thanks Mike for your response.

ScottPletcher · March 9, 2023, 4:00pm

Is there any chance of tied max value? That is, the high amount being 5 for both, say, Col_A and Col_C?

Mike_1980 · March 10, 2023, 11:03am

Yes, now I think of it, the chance is small, but as a matter of fact there could be, and it seems that in the code from harishgg1 the Unknown value isn't added to either one of them if this is the case.

Can you perhaps suggest a solution for this..?

harishgg1 · March 10, 2023, 12:01pm

Perhaps

Change all >

To >=

harishgg1 · March 10, 2023, 4:41pm

hi

hope this helps

>= will work

create data script

drop table if exists #table1
CREATE TABLE #table1
(Date1 date, Col_A varchar(1), Col_B int, Col_C int, Unknown int)
INSERT INTO #table1
(Date1, Col_A, Col_B, Col_C, Unknown)
VALUES
('1-3-2023',5,1,5,1),
('2-3-2023',6,6,1,2),
('3-3-2023',0,0,1,1),
('4-3-2023',5,0,2,3)

select       
     date1
  ,  case when col_a >= col_b and col_a >= col_c then  col_a + Unknown else col_a end   Col_A                 
  ,  case when col_b >= col_a and col_b >= col_c then  col_b + Unknown else col_b end   Col_B                 
  ,  case when col_c >= col_b and col_c > =col_a then  col_c + Unknown else col_c end   Col_C
from 
   #table1 t

Mike_1980 · March 15, 2023, 8:25am

Thanks a lot, harishgg1...