How can I detect the maximum value in several rows and columns at the same time in an SQL table?

Question

I have a table which records values over time, similar to the following:

The next value after the zero value indicates the beginning of the highest value to one case for a given time. Example: To Time6 the maximum value is 5 because only case4 Start (after zero value) in that moment the others continuing decreasing at that time.

I need to know at a given moment what is the maximum value case for all the times shown and gradually place it as shown in the following table:

I have worked with some of the aggregate and analytic functions, but can't seem to find the correct solution especially because query is to a table of 40 million records. I hope an SQL expert guru can help me out, or at least point me in the right direction or some efficient algorithm. Is there a way to query this (preferably without a cursor?)

Thanks in advance.

Answer 1

If I understood what you are trying to do correctly then this should work (fiddle).

The CASE expression ensures that only values with 0 in the preceding column are considered for the aggregation. Prior to the aggregation the integers (presumed >= 0) are padded out with leading zeroes to make them exactly 10 characters long and concatenated to the Cases string.

The MAX is then found to get the lexicographically greatest concatenated string for that time (as here) and then this is split into its constituent parts.

In the event that there is more than one eligible row with the same maximum value for a specific time this will somwehat arbitrarily pick the one with the alphabetically latest Cases string

WITH Grouped As
(
SELECT [ID], 
      MAX(CASE WHEN [Time2] = 0 THEN FORMAT([Time3],'D10') + [Cases] END) AS TC3, 
      MAX(CASE WHEN [Time3] = 0 THEN FORMAT([Time4],'D10') + [Cases] END) AS TC4, 
      MAX(CASE WHEN [Time4] = 0 THEN FORMAT([Time5],'D10') + [Cases] END) AS TC5, 
      MAX(CASE WHEN [Time5] = 0 THEN FORMAT([Time6],'D10') + [Cases] END) AS TC6, 
      MAX(CASE WHEN [Time6] = 0 THEN FORMAT([Time7],'D10') + [Cases] END) AS TC7, 
      MAX(CASE WHEN [Time7] = 0 THEN FORMAT([Time8],'D10') + [Cases] END) AS TC8, 
      MAX(CASE WHEN [Time8] = 0 THEN FORMAT([Time9],'D10') + [Cases] END) AS TC9
FROM YourTable
GROUP BY [ID]
)
SELECT [ID], 
       Time3 = 0+LEFT(TC3,10),
       Cases3 = SUBSTRING(TC3,11,8000),
       Time4 = 0+LEFT(TC4,10),
       Cases4 = SUBSTRING(TC4,11,8000),
       Time5 = 0+LEFT(TC5,10),
       Cases5 = SUBSTRING(TC5,11,8000),
       Time6 = 0+LEFT(TC6,10),
       Cases6 = SUBSTRING(TC6,11,8000),
       Time7 = 0+LEFT(TC7,10),
       Cases7 = SUBSTRING(TC7,11,8000),
       Time8 = 0+LEFT(TC8,10),
       Cases8 = SUBSTRING(TC8,11,8000),
       Time9 = 0+LEFT(TC9,10),
       Cases9 = SUBSTRING(TC9,11,8000)
FROM Grouped

Answer 2

Honestly, I don't know how will you create such table dynamically, but for now I created table only with 5 Time columns for testing

CREATE TABLE times (id int IDENTITY (1,1),Cases varchar(5),Time1 int,Time2 int,Time3 int,Time4 int, Time5 int)

INSERT INTO times VALUES ('CASE1',8,0,10,9,8),('CASE2',2,1,0,0,8),('CASE3',5,4,0,7,6),('CASE4',0,5,0,5,0),('CASE5',4,0,0,9,8),('CASE6',0,6,0,0,6)

I know you not prefer loops, but I think it'll help you somehow

CREATE TABLE #temp (Times varchar(100),Cases varchar(100),Value int)
DECLARE @time_min_nr varchar(1)=1, --Times column minimum value
@time_max_nr varchar(3)=5, --Times column maximum value
@time_big_nr varchar(3),
@query nvarchar(1000)
WHILE @time_min_nr<@time_max_nr
BEGIN
SET @time_big_nr=@time_min_nr+1
SET @query='INSERT INTO #temp SELECT TOP 1 ''Time'+@time_big_nr+''' Times,Cases,MAX(Time'+@time_big_nr+')-MIN(TIME'+@time_min_nr+') Value  FROM times GROUP BY Cases ORDER BY Value DESC'
EXEC sp_executesql @query
SET @time_min_nr+=1
END
SELECT * FROM #temp