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
