Delivery API - Reporte pedidos por hora por dia

/*
    Obtener pedidos por hora por día y la ultima columna es un promedio.
*/

DECLARE @ColumnasDinamicas NVARCHAR(MAX);
DECLARE @ConsultaFinal NVARCHAR(MAX);
DECLARE @FechaInicio DATE = '2024-11-21'; /*DESDE ESTE DIA EN ADELANTE*/

-- Construir las columnas dinámicamente usando fechas existentes en la tabla
SELECT @ColumnasDinamicas = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(CAST(fechacreado AS DATE))
    FROM ordenes
    WHERE CAST(fechacreado AS DATE) BETWEEN @FechaInicio AND CAST(GETDATE() AS DATE)
    FOR XML PATH(''), TYPE
).VALUE('.', 'NVARCHAR(MAX)'), 1, 1, '');

IF @ColumnasDinamicas IS NULL
BEGIN
    PRINT 'No hay datos para las fechas especificadas';
    RETURN;
END;

SET @ConsultaFinal = '
SELECT
    Hora,
    ' + @ColumnasDinamicas + ',
    CAST((ISNULL(' +
    STUFF((
        SELECT '+ISNULL(' + QUOTENAME(CAST(fechacreado AS DATE)) + ',0)'
        FROM ordenes
        WHERE CAST(fechacreado AS DATE) BETWEEN @FechaInicio AND CAST(GETDATE() AS DATE)
        GROUP BY CAST(fechacreado AS DATE)
        FOR XML PATH('')
    ), 1, 1, '') + ', 0)) /
    (SELECT COUNT(DISTINCT CAST(fechacreado AS DATE))
     FROM ordenes
     WHERE CAST(fechacreado AS DATE) BETWEEN ''' + CAST(@FechaInicio AS VARCHAR(10)) + ''' AND CAST(GETDATE() AS DATE)
    ) AS DECIMAL(10,2)) as Promedio_Fila
FROM (
    SELECT
        DATEPART(HOUR, fechacreado) AS Hora,
        CAST(fechacreado AS DATE) AS Fecha,
        COUNT(*) AS Cantidad
    FROM ordenes
    WHERE CAST(fechacreado AS DATE) BETWEEN ''' + CAST(@FechaInicio AS VARCHAR(10)) + ''' AND CAST(GETDATE() AS DATE)
    GROUP BY DATEPART(HOUR, fechacreado), CAST(fechacreado AS DATE)
) AS SourceTable
PIVOT (
    SUM(Cantidad)
    FOR Fecha IN (' + @ColumnasDinamicas + ')
) AS PivotTable
ORDER BY Hora;';

EXEC sp_executesql @ConsultaFinal;