CI3 error with query SQLSERVER

Hi everyone, 

I have this query if I run it in sqlstudio it works 

The query  

PHP Code:

SELECT * INTO #TempTable FROM ( SELECT VDN,YEAR(FECHA) AS AÑO,MONTH(FECHA) AS MES,FORMAT(FECHA,'MMMM') AS MES_NOMBRE, SUM(INCALLS - ABNCALLS) AS CALLS FROM [dbo].[CCOD_TRAFICO_INTERVALO] WHERE VDN IN (71072,50001,50002,50003,50004,50005,50006,50007,50039,50040) AND FECHA >= '2020-10-01' GROUP BY VDN,YEAR(FECHA),MONTH(FECHA),FORMAT(FECHA,'MMMM') ) T; DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SET @cols = (SELECT STRING_AGG(MES_NOMBRE,',') FROM (SELECT DISTINCT AÑO,MES,MES_NOMBRE FROM #TempTable)t); SET @query = 'SELECT VDN, ' + @cols + ' FROM ( SELECT VDN,MES_NOMBRE,CALLS FROM #TempTable ) X PIVOT ( SUM(CALLS) FOR MES_NOMBRE IN (' + @cols + ')) P '; execute(@query); drop table #TempTable 


as a result I got a table

Code:

VDN October November December January
50001 200     920      2346    4248
50002 1209   2173      1548    1681
50003 33      135      374      649
50004 419     1054     504      356
50005 5329    8115     5619    5250
50006 13550   14093    9497    9563
50007 5602     6180    4008    3978
50039 2256     2161    1449    1236
50040 5306     5370    3752    4035
71072 19482    22907   17681   19712

but when I put it funcion in the model 

PHP Code:

The model.

public function get_calls_by_vdn_month_year($vdns)
    {
        $query = $this->db_azu1->query("SELECT * INTO #TempTable FROM ( 
                                            SELECT VDN,YEAR(FECHA) AS AÑO,MONTH(FECHA) AS MES,FORMAT(FECHA,'MMMM') AS MES_NOMBRE, SUM(INCALLS - ABNCALLS) AS CALLS 
                                            FROM [dbo].[CCOD_TRAFICO_INTERVALO] WHERE VDN IN (71072,50001,50002,50003,50004,50005,50006,50007,50039,50040) 
                                            AND FECHA >= '2020-10-01' GROUP BY VDN,YEAR(FECHA),MONTH(FECHA),FORMAT(FECHA,'MMMM') ) T; 
                                        DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); 
                                        SET @cols = (SELECT STRING_AGG(MES_NOMBRE,',') FROM (SELECT DISTINCT AÑO,MES,MES_NOMBRE FROM #TempTable)t); 
                                        SET @query = 'SELECT VDN, ' + @cols + ' FROM ( SELECT VDN,MES_NOMBRE,CALLS FROM #TempTable ) X PIVOT ( SUM(CALLS) FOR MES_NOMBRE IN (' + @cols + ')) P '; 
                                        execute(@query); 
                                        drop table #TempTable");
        return $query->result();
    }

the Controller.

 $calls = $this->consultas->get_calls_by_vdn_month_year($vdns);

        echo "<pre>";
        print_r($calls);
        echo "</pre>";
        exit(); 


and print into controller, the result is Array() and I dont know  why. Any idea?

Regards

@Y6700B,

You have a lot going on there. Can you do me a favor and format it. This will allow us to read it better.

Hi I think CI has a problem when I use pivot function in sqlserver, I made another test usign the next query

PHP Code:

SELECT * FROM ( 
SELECT DAY(CC.FECHA) AS DIA,CC.DOMINIO,CC.INCALLS
FROM [dbo].[CCOD_INFORME_COLA_DIARIO_AUX] AS CC 
LEFT JOIN [dbo].[COLAS] AS C ON C.COLA = CC.COLA
WHERE CC.AÑO = 2021 AND MONTH(CC.FECHA) = 1 AND C.IDAREA = 1 
) X PIVOT ( SUM(INCALLS) FOR DIA IN ([4],[5])) P 


It´s simple but when I print the result in the controller only show me.

PHP Code:

(
    [0] => stdClass Object
        (
            [DOMINIO] => IT HELP DESK
        )

    [1] => stdClass Object
        (
            [DOMINIO] => PAYROLL
        )

) 


When If I run it in SQlServerStudio gave a table that I want

Code:

DOMINIO             4    5
IT HELP DESK    14    19
PAYROLL            484    437

Anyone could fix this or have an example?

@Y6700B,

Thx for the reformat. I have one suggestion and 1 question.

Suggestion: If you are not expecting a dynamic variable to be a part of the query then why not make your query a database VIEW within MSSQL Server and then call the view.

Question: what error message are you seeing when you run it in CI?

Whatever you are trying to do, I do not think this is the most efficient way to do it.

@Y6700B,

Thx for the reformat.  I have one suggestion and 1 question.

Suggestion: If you are not expecting a dynamic variable to be a part of the query then why not make your query a database VIEW within MSSQL Server and then call the view.

Question: what error message are you seeing when you run it in CI?

Suggestion: The function is dinamyc only for the example I didnot set variables.
Question: There´s no error message the only think is the function from de model doesnt return all info and return an empty array.

@Y6700B,

If the query does not need to have variables added then why not make it a MSSQL query then all you have to do in CI4 is call the query.