I have a pivoted query that returns the count of events in a table (matrix) that compares week day vs time of day:

;WITH [pivotData] as
(
    SELECT  
        DATEPART(WEEKDAY, [Jc].[Fecha]) as [Day],
        DATEPART(HOUR, [Jc].[Fecha]) as [Hour],
        [Jc].[ID] as [ID]
    FROM [dbo].[Jornadas_Cronograma] [Jc]
    WHERE 
        ([Jc].[IdEstadoGeneral] = 1)
)
    SELECT 
        CASE 
            [Day] 
            WHEN 1 THEN 'Domingo'       
            WHEN 2 THEN 'Lunes'        
            WHEN 3 THEN 'Martes'        
            WHEN 4 THEN 'Miércoles'       
            WHEN 5 THEN 'Jueves'        
            WHEN 6 THEN 'Viernes'        
            WHEN 7 THEN 'Sábado' 
        END AS [Dia],
        [0] AS [00:00],
        [1] AS [01:00],
        [2] AS [02:00],
        [3] AS [03:00],
        [4] AS [04:00],
        [5] AS [05:00],
        [6] AS [06:00],
        [7] AS [07:00],
        [8] AS [08:00],
        [9] AS [09:00],
        [10] AS [10:00],
        [11] AS [11:00],
        [12] AS [12:00],
        [13] AS [13:00],
        [14] AS [14:00],
        [15] AS [15:00],
        [16] AS [16:00],
        [17] AS [17:00],
        [18] AS [18:00],
        [19] AS [19:00],
        [20] AS [20:00],
        [21] AS [21:00],
        [22] AS [22:00],
        [23] AS [23:00]
    FROM [pivotData]
        PIVOT
            (
                COUNT([ID]) FOR [Hour] 
                IN 
                (
                    [0], [1], [2], [3], [4], [5], [6], [7], [8], [9],[10],[11],[12],[13],[14],[15],[16],[17],[18], [19], [20], [21], [22], [23]
                )
            ) AS [pivoted]
    ORDER BY
        [Day];

This will return this result:

I need to get the max value from all the columns (in this example it will be 93, for Wednesday 08:00) and store it in a variable, integer, or so.. to operate it in the front end

In my app, I will render this info to a DataTable.

I've tried to iterate through each column of the datatable and store the .max value into a list, but it's not working as expected, and I'm stalled at this point.

I'm guessing the best approach will be in C# rather than SQL, but I'm a beginner, and I have no more ideas!

I iterated through columns, try to store the values in a list, and then try to get the max value within that list. In this example I'm iterating on columns 00:00 and 06:00, for testing purposes:

        gvMapaHoras.DataSource = traerMapa();
        gvMapaHoras.DataBind();

        List<List<int>> miLista = new List<List<int>>();
        
        int mxTotal = 0;
        
        foreach (GridViewRow fila in gvMapaHoras.Rows)
        {

            Literal lt00 = (Literal)fila.FindControl("lt00");
            int vl00 = Convert.ToInt32(lt00.Text);

            Literal lt06 = (Literal)fila.FindControl("lt06");
            int vl06 = Convert.ToInt32(lt06.Text);

            miLista.Add(new List<int> { vl00, vl06 });

        }

        mxTotal = miLista.Max();

But my error at this point is that I can't relate a List into a integer.

To populate the gridview I created a class:

 protected DataTable traerMapa()
    {

        DataTable dt2 = new DataTable();
        using (SqlConnection conx2 = new SqlConnection(enchufe0))
        {
            using (SqlCommand cmd2 = new SqlCommand(spQuerySel2, conx2))
            {
                cmd2.CommandType = CommandType.StoredProcedure;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd2))
                {
                    sda.Fill(dt2);
                }
            }
        }

        return dt2;

    }

Any suggestion?

CodePudding user response：

A SQL SERVER approach

Here we

1. Aggregate the data in the 1st CTE pivotData
2. Calculate the maxValue in cteMaxVal
3. Then we simply apply the maxValue to the pivoted results via a CROSS JOIN

Example

;WITH [pivotData] as
(
    SELECT  
        DATEPART(WEEKDAY, [Jc].[Fecha]) as [Day],
        DATEPART(HOUR, [Jc].[Fecha]) as [Hour],
        Count(*) as Cnt
    FROM  [dbo].[Jornadas_Cronograma] [Jc]
    WHERE ([Jc].[IdEstadoGeneral] = 1)
    GROUP BY DATEPART(WEEKDAY, [Jc].[Fecha]),
             DATEPART(HOUR, [Jc].[Fecha])
), cteMaxVal as (
    Select MaxVal=max(Cnt) from [pivotData]
)
    SELECT 
        CASE 
            [Day] 
            WHEN 1 THEN 'Domingo'       
            WHEN 2 THEN 'Lunes'        
            WHEN 3 THEN 'Martes'        
            WHEN 4 THEN 'Miércoles'       
            WHEN 5 THEN 'Jueves'        
            WHEN 6 THEN 'Viernes'        
            WHEN 7 THEN 'Sábado' 
        END AS [Dia],
        [0] AS [00:00],
        [1] AS [01:00],
        [2] AS [02:00],
        [3] AS [03:00],
        [4] AS [04:00],
        [5] AS [05:00],
        [6] AS [06:00],
        [7] AS [07:00],
        [8] AS [08:00],
        [9] AS [09:00],
        [10] AS [10:00],
        [11] AS [11:00],
        [12] AS [12:00],
        [13] AS [13:00],
        [14] AS [14:00],
        [15] AS [15:00],
        [16] AS [16:00],
        [17] AS [17:00],
        [18] AS [18:00],
        [19] AS [19:00],
        [20] AS [20:00],
        [21] AS [21:00],
        [22] AS [22:00],
        [23] AS [23:00],
        MaxVal
    FROM [pivotData]
        PIVOT
            (
                sum(Cnt) FOR [Hour] 
                IN 
                (
                    [0], [1], [2], [3], [4], [5], [6], [7], [8], [9],[10],[11],[12],[13],[14],[15],[16],[17],[18], [19], [20], [21], [22], [23]
                )
            ) AS [pivoted]
    Cross JOIN cteMaxVal
    ORDER BY
        [Day];