Modify SQL query to get response with desired structure

Navigation
1

I am trying to create a query that allows me to show (in different columns) the budget for each month.

This is the query I'm using: 

SELECT TOP 12 CL.Cliente_DS, 
       EJE.Ejecutivo_DS, 
       DS.Disciplina_DS,
       LN.Linea_Negocio_DS,
       CASE WHEN MONTH(FP.Fecha) = 1 THEN SUM(FP.Presupuesto) END AS Budget_Enero,
       CASE WHEN MONTH(FP.Fecha) = 2 THEN SUM(FP.Presupuesto) END AS Budget_Febrero,
       CASE WHEN MONTH(FP.Fecha) = 3 THEN SUM(FP.Presupuesto) END AS Budget_Marzo,
       CASE WHEN MONTH(FP.Fecha) = 4 THEN SUM(FP.Presupuesto) END AS Budget_Abril,
       CASE WHEN MONTH(FP.Fecha) = 5 THEN SUM(FP.Presupuesto) END AS Budget_Mayo,
       CASE WHEN MONTH(FP.Fecha) = 6 THEN SUM(FP.Presupuesto) END AS Budget_Junio,
       CASE WHEN MONTH(FP.Fecha) = 7 THEN SUM(FP.Presupuesto) END AS Budget_Julio,
       CASE WHEN MONTH(FP.Fecha) = 8 THEN SUM(FP.Presupuesto) END AS Budget_Agosto,
       CASE WHEN MONTH(FP.Fecha) = 9 THEN SUM(FP.Presupuesto) END AS Budget_Septiembre,
       CASE WHEN MONTH(FP.Fecha) = 10 THEN SUM(FP.Presupuesto) END AS Budget_Octubre,
       CASE WHEN MONTH(FP.Fecha) = 11 THEN SUM(FP.Presupuesto) END AS Budget_Noviembre,
       CASE WHEN MONTH(FP.Fecha) = 12 THEN SUM(FP.Presupuesto) END AS Budget_Diciembre
FROM FACT_PRESUPUESTO AS FP INNER JOIN DIM_Cliente AS CL
ON FP.Sk_Cliente = CL.Sk_Cliente INNER JOIN DIM_TIPO_TRABAJO AS TT
ON FP.Sk_Tipo_Trabajo = TT.Sk_Tipo_Trabajo INNER JOIN DIM_LINEA_NEGOCIO AS LN
ON TT.Sk_Linea_Negocio = LN.SK_Linea_Negocio INNER JOIN DIM_DISCIPLINA AS DS
ON LN.Sk_Disciplina = DS.SK_Disciplina LEFT JOIN DIM_EJECUTIVO AS EJE
ON CL.Sk_Ejecutivo = EJE.Sk_Ejecutivo
WHERE YEAR(FP.Fecha) = 2017
AND Cliente_DS = 'MASTERCARD MDF MIAM '
GROUP BY CL.Cliente_DS, EJE.Ejecutivo_DS, DS.Disciplina_DS, LN.Linea_Negocio_DS, FP.Fecha;

The structure of the answer should look like this:

Example: 

Cliente_DS           Ejecutivo_DS Disciplina_DS Linea_Negocio_DS   Budget_Enero           Budget_Febrero         Budget_Marzo           Budget_Abril           Budget_Mayo            Budget_Junio           Budget_Julio           Budget_Agosto          Budget_Septiembre      Budget_Octubre         Budget_Noviembre       Budget_Diciembre      
-------------------- ------------ ------------- ------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) 0                      0                      0                      0                      0                      0                      0                      0                      0                      0                      0                      0

But I'm getting this answer (that is, each budget is added as a new row in the SELECT) : 

Cliente_DS           Ejecutivo_DS Disciplina_DS Linea_Negocio_DS   Budget_Enero           Budget_Febrero         Budget_Marzo           Budget_Abril           Budget_Mayo            Budget_Junio           Budget_Julio           Budget_Agosto          Budget_Septiembre      Budget_Octubre         Budget_Noviembre       Budget_Diciembre      
-------------------- ------------ ------------- ------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) 0                      NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   0                      NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   0                      NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   0                      NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   0                      NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   NULL                   0                      NULL                   NULL                   NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   0                      NULL                   NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   0                      NULL                   NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   0                      NULL                   NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   0                      NULL                   NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   0                      NULL                  
MASTERCARD MDF MIAM  NULL         ATL           Alianzas MDF (ATL) NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   NULL                   0

I was thinking of saving the results of the query in a variable of type TABLE , but I do not know how to save the budget according to the month "in the corresponding column".

Something like I illustrate in this pseudo-code: 

-- Tabla temporal que guardará los resultados finales.
DECLARE @tbl AS TABLE (
    Id INT IDENTITY(1,1),
    Budget_Enero AS FLOAT,
    Budget_Febrero AS FLOAT,
    -- etcétera.
);

-- Insertar los resultados en la variable TABLE:
INSERT INTO @tbl (/* Campos de la respuesta */)
SELECT TOP 12 CL.Cliente_DS, 
       EJE.Ejecutivo_DS, 
       DS.Disciplina_DS,
       LN.Linea_Negocio_DS,
       CASE WHEN MONTH(FP.Fecha) = 1 THEN SUM(FP.Presupuesto) END AS Budget_Enero,
       CASE WHEN MONTH(FP.Fecha) = 2 THEN SUM(FP.Presupuesto) END AS Budget_Febrero,
       CASE WHEN MONTH(FP.Fecha) = 3 THEN SUM(FP.Presupuesto) END AS Budget_Marzo,
       CASE WHEN MONTH(FP.Fecha) = 4 THEN SUM(FP.Presupuesto) END AS Budget_Abril,
       CASE WHEN MONTH(FP.Fecha) = 5 THEN SUM(FP.Presupuesto) END AS Budget_Mayo,
       CASE WHEN MONTH(FP.Fecha) = 6 THEN SUM(FP.Presupuesto) END AS Budget_Junio,
       CASE WHEN MONTH(FP.Fecha) = 7 THEN SUM(FP.Presupuesto) END AS Budget_Julio,
       CASE WHEN MONTH(FP.Fecha) = 8 THEN SUM(FP.Presupuesto) END AS Budget_Agosto,
       CASE WHEN MONTH(FP.Fecha) = 9 THEN SUM(FP.Presupuesto) END AS Budget_Septiembre,
       CASE WHEN MONTH(FP.Fecha) = 10 THEN SUM(FP.Presupuesto) END AS Budget_Octubre,
       CASE WHEN MONTH(FP.Fecha) = 11 THEN SUM(FP.Presupuesto) END AS Budget_Noviembre,
       CASE WHEN MONTH(FP.Fecha) = 12 THEN SUM(FP.Presupuesto) END AS Budget_Diciembre
FROM FACT_PRESUPUESTO AS FP INNER JOIN DIM_Cliente AS CL
ON FP.Sk_Cliente = CL.Sk_Cliente INNER JOIN DIM_TIPO_TRABAJO AS TT
ON FP.Sk_Tipo_Trabajo = TT.Sk_Tipo_Trabajo INNER JOIN DIM_LINEA_NEGOCIO AS LN
ON TT.Sk_Linea_Negocio = LN.SK_Linea_Negocio INNER JOIN DIM_DISCIPLINA AS DS
ON LN.Sk_Disciplina = DS.SK_Disciplina LEFT JOIN DIM_EJECUTIVO AS EJE
ON CL.Sk_Ejecutivo = EJE.Sk_Ejecutivo
WHERE YEAR(FP.Fecha) = 2017
AND Cliente_DS = 'MASTERCARD MDF MIAM '
GROUP BY CL.Cliente_DS, EJE.Ejecutivo_DS, DS.Disciplina_DS, LN.Linea_Negocio_DS, FP.Fecha;

-- Recorrer la tabla para actualizar en las columnas con valores NULL
-- los presupuestos según el mes:
DECLARE @incr AS INT = 0;
DECLARE @cantidadDatos AS INT = (SELECT COUNT(Budget_Enero) FROM @tbl);
DECLARE @budget_next_row  AS FLOAT; -- Se usa para obtener el valor de "Budget_" de la siguiente fila.

WHILE @incr < @cantidadDatos
BEGIN
    SET @incr = @incr + 1;
    SET @budget_next_row = (SELECT Budget_Febrero FROM @tbl WHERE Id = (@incr + 1));

    -- Detectar si la columna "Budget_Febrero" de la fila siguiente a la actual es NULL:
    IF (SELECT ISNULL(Budget_Febrero, 0) FROM @tbl WHERE Id = @incr) == 0 AND @budget_next_row IS NOT NULL
    BEGIN
        -- Actualizar la fila.
        UPDATE @tbl
        SET Budget_Febrero = @budget_next_row
        WHERE Id = @incr;
    END

    -- Y así con las demás columnas.
END

-- Consultar los resultados depurados.
-- Otra opción puede ser eliminar los registros de budget = NULL...
SELECT *
FROM @tbl
WHERE Budget_Enero IS NOT NULL
AND Budget_Febrero IS NOT NULL
---...

How can I modify this query (or how can I create a new query logic) to get the expected results?

    
asked by Mauricio Arias Olave 07.03.2018 в 17:18
source

1 answer

1

The immediate change is quite simple, you are simply using the aggregation function wrong, it should be out of the expression CASE : 

SELECT TOP 12 CL.Cliente_DS, 
       EJE.Ejecutivo_DS, 
       DS.Disciplina_DS,
       LN.Linea_Negocio_DS,
       SUM(CASE WHEN MONTH(FP.Fecha) = 1 THEN FP.Presupuesto END) AS Budget_Enero,
       SUM(CASE WHEN MONTH(FP.Fecha) = 2 THEN FP.Presupuesto END) AS Budget_Febrero,
       SUM(CASE WHEN MONTH(FP.Fecha) = 3 THEN FP.Presupuesto END) AS Budget_Marzo,
       SUM(CASE WHEN MONTH(FP.Fecha) = 4 THEN FP.Presupuesto END) AS Budget_Abril,
       SUM(CASE WHEN MONTH(FP.Fecha) = 5 THEN FP.Presupuesto END) AS Budget_Mayo,
       SUM(CASE WHEN MONTH(FP.Fecha) = 6 THEN FP.Presupuesto END) AS Budget_Junio,
       SUM(CASE WHEN MONTH(FP.Fecha) = 7 THEN FP.Presupuesto END) AS Budget_Julio,
       SUM(CASE WHEN MONTH(FP.Fecha) = 8 THEN FP.Presupuesto END) AS Budget_Agosto,
       SUM(CASE WHEN MONTH(FP.Fecha) = 9 THEN FP.Presupuesto END) AS Budget_Septiembre,
       SUM(CASE WHEN MONTH(FP.Fecha) = 10 THEN FP.Presupuesto END) AS Budget_Octubre,
       SUM(CASE WHEN MONTH(FP.Fecha) = 11 THEN FP.Presupuesto END) AS Budget_Noviembre,
       SUM(CASE WHEN MONTH(FP.Fecha) = 12 THEN FP.Presupuesto END) AS Budget_Diciembre
FROM FACT_PRESUPUESTO AS FP INNER JOIN DIM_Cliente AS CL
ON FP.Sk_Cliente = CL.Sk_Cliente INNER JOIN DIM_TIPO_TRABAJO AS TT
ON FP.Sk_Tipo_Trabajo = TT.Sk_Tipo_Trabajo INNER JOIN DIM_LINEA_NEGOCIO AS LN
ON TT.Sk_Linea_Negocio = LN.SK_Linea_Negocio INNER JOIN DIM_DISCIPLINA AS DS
ON LN.Sk_Disciplina = DS.SK_Disciplina LEFT JOIN DIM_EJECUTIVO AS EJE
ON CL.Sk_Ejecutivo = EJE.Sk_Ejecutivo
WHERE YEAR(FP.Fecha) = 2017
AND Cliente_DS = 'MASTERCARD MDF MIAM '
GROUP BY CL.Cliente_DS, EJE.Ejecutivo_DS, DS.Disciplina_DS, LN.Linea_Negocio_DS;

That said, a couple of recommendations. Do not use TOP without a ORDER BY , since it does not make much sense. Another recommendation is that you do not use a function on the Fecha column, since this way you prevent this query from using an index if you have one. 

SELECT CL.Cliente_DS, 
       EJE.Ejecutivo_DS, 
       DS.Disciplina_DS,
       LN.Linea_Negocio_DS,
       SUM(CASE WHEN FP.Fecha >= '20170101' AND FP.Fecha < '20170201' THEN FP.Presupuesto END) AS Budget_Enero,
       SUM(CASE WHEN FP.Fecha >= '20170201' AND FP.Fecha < '20170301' THEN FP.Presupuesto END) AS Budget_Febrero,
       SUM(CASE WHEN FP.Fecha >= '20170301' AND FP.Fecha < '20170401' THEN FP.Presupuesto END) AS Budget_Marzo,
       SUM(CASE WHEN FP.Fecha >= '20170401' AND FP.Fecha < '20170501' THEN FP.Presupuesto END) AS Budget_Abril,
       SUM(CASE WHEN FP.Fecha >= '20170501' AND FP.Fecha < '20170601' THEN FP.Presupuesto END) AS Budget_Mayo,
       SUM(CASE WHEN FP.Fecha >= '20170601' AND FP.Fecha < '20170701' THEN FP.Presupuesto END) AS Budget_Junio,
       SUM(CASE WHEN FP.Fecha >= '20170701' AND FP.Fecha < '20170801' THEN FP.Presupuesto END) AS Budget_Julio,
       SUM(CASE WHEN FP.Fecha >= '20170801' AND FP.Fecha < '20170901' THEN FP.Presupuesto END) AS Budget_Agosto,
       SUM(CASE WHEN FP.Fecha >= '20170901' AND FP.Fecha < '20171001' THEN FP.Presupuesto END) AS Budget_Septiembre,
       SUM(CASE WHEN FP.Fecha >= '20171001' AND FP.Fecha < '20171101' THEN FP.Presupuesto END) AS Budget_Octubre,
       SUM(CASE WHEN FP.Fecha >= '20171101' AND FP.Fecha < '20171201' THEN FP.Presupuesto END) AS Budget_Noviembre,
       SUM(CASE WHEN FP.Fecha >= '20171201' AND FP.Fecha < '20180101' THEN FP.Presupuesto END) AS Budget_Diciembre
FROM FACT_PRESUPUESTO AS FP 
INNER JOIN DIM_Cliente AS CL
    ON FP.Sk_Cliente = CL.Sk_Cliente 
INNER JOIN DIM_TIPO_TRABAJO AS TT
    ON FP.Sk_Tipo_Trabajo = TT.Sk_Tipo_Trabajo 
INNER JOIN DIM_LINEA_NEGOCIO AS LN
    ON TT.Sk_Linea_Negocio = LN.SK_Linea_Negocio 
INNER JOIN DIM_DISCIPLINA AS DS
    ON LN.Sk_Disciplina = DS.SK_Disciplina 
LEFT JOIN DIM_EJECUTIVO AS EJE
    ON CL.Sk_Ejecutivo = EJE.Sk_Ejecutivo
WHERE FP.Fecha >= '20170101' AND Fecha < '20180101'
AND Cliente_DS = 'MASTERCARD MDF MIAM '
GROUP BY CL.Cliente_DS, 
         EJE.Ejecutivo_DS, 
         DS.Disciplina_DS, 
         LN.Linea_Negocio_D
;
    
answered by 07.03.2018 / 17:21
source
Add Apps script to Google sites How to compress an XML file in ZIP in VBA Excel?