none
Problema com referencia cruzada RRS feed

  • Pergunta

  • Pessoal, bom dia.

    Estou com problema no TSQL abaixo:

    SELECT reference_name as 'AGOSTO', [1], [2], [3], [4] ,[5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21],[22],[23], [24] ,[25], [26], [27], [28], [29],[30]
    from (
     
    select convert(char,creation_date,103) creation_date, reference_id, sum(total)total, reference_name from plan1$ where creation_date >= '2010-08-01' 
    and creation_date < '2010-08-30' and carrier_id = 1 and application_id = 53
    group by reference_id , creation_date, reference_name )p
    PIVOT 
    (
    sum(total)
    FOR day(creation_date) in 
    ([1], [2], [3], [4] ,[5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21],[22],[23], [24] ,[25], [26], [27], [28], [29],[30])
    )AS PivotTable

    Acontece que recebo o erro:  Incorrect syntax near '('.

    Eu preciso que para cada coluna referente aos dias de agosto seja exibido o valor da soma do campo total.

    Eu tentei o código abaixo e o mesmo funciona, no entanto ele mostrar o valor da soma do campo total na coluna com o mesmo valor da soma e não no dia refrente ao campo creation_date

    SELECT reference_name as 'AGOSTO', [1], [2], [3], [4] ,[5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21],[22],[23], [24] ,[25], [26], [27], [28], [29],[30]
    from (
     
    select convert(char,creation_date,103) creation_date, reference_id, sum(total)total, reference_name from plan1$ where creation_date >= '2010-08-01' 
    and creation_date < '2010-08-30' and carrier_id = 1 and application_id = 53
    group by reference_id , creation_date, reference_name )p
    PIVOT 
    (
    sum(total)
    FOR total in 
    ([1], [2], [3], [4] ,[5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21],[22],[23], [24] ,[25], [26], [27], [28], [29],[30])
    )AS PivotTable

    Alguem poderia me ajudar? è muito urgente!

    Grato


    Analista Programador
    terça-feira, 9 de novembro de 2010 12:45

Respostas

  • Consegui!

    SELECT reference_name as 'AGOSTO', [1], [2], [3], [4] ,[5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21],[22],[23], [24] ,[25], [26], [27], [28], [29],[30]
    from (
     
    select convert(char,creation_date,103) creation_date,day(creation_date)as dia, reference_id, sum(total)total, reference_name from plan1$ where creation_date >= '2010-08-01'
    and creation_date < '2010-08-30' and carrier_id = 1 and application_id = 53
    group by reference_id , creation_date, reference_name )p
    PIVOT
    (
    sum(total)
    FOR dia in
    ([1], [2], [3], [4] ,[5], [6], [7], [8], [9], [10],
    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21],[22],[23], [24] ,[25], [26], [27], [28], [29],[30])
    )AS PivotTable


    Analista Programador
    • Marcado como Resposta gusbr terça-feira, 9 de novembro de 2010 13:39
    terça-feira, 9 de novembro de 2010 13:39