Hello, I have the following query:
set NOCOUNT on;
declare @_mes int = 2
declare @_ano int = 2016
declare @_dataini datetime = CONVERT(DATETIME, '01/' + REPLICATE('0', 2 - LEN(@_MES)) + CAST(@_MES AS VARCHAR(2)) + '/' + CAST(@_ANO AS VARCHAR(4)), 103)
declare @_datafim datetime = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @_dataini)+1,0))
declare @_dataaux datetime
declare @_cnpj_cpf varchar(14)
declare @_razao varchar(100)
declare @_fantasia varchar(100)
declare @_colunas varchar(max) = ''
declare @_comando varchar(max) = ''
exec('drop table #TEMPSCANN')
CREATE TABLE #TEMPSCANN (CNPJ_CPF VARCHAR(14), RAZAO VARCHAR(100) , FANTASIA VARCHAR(100))
select @_dataaux = @_dataini
While (@_dataaux <= @_datafim)
begin
DECLARE @_coma as varchar(100) = 'alter table #TEMPSCANN add [' + convert(varchar(10), @_dataaux, 103) + '] DATETIME'
exec(@_coma)
select @_dataaux = DATEADD(DAY, 1, @_dataaux)
end
DECLARE scann_cursor CURSOR FOR
SELECT C.CNPJ_CPF, C.RAZAO, C.FANTASIA
FROM CLIENTES C, GWADMCLI ADM, GWITMADMCLI ITM
WHERE C.CODCLIE = ADM.CODCLIE
AND ADM.CODADMCLI = ITM.CODADMCLI
AND ITM.CODPROD = 1365
AND ITM.SITUACAO IN ('G','C')
AND ADM.CONTRATO IN ('G','S')
AND C.ATIVO = 'S'
ORDER BY C.CNPJ_CPF;
OPEN scann_cursor
FETCH NEXT FROM scann_cursor
INTO @_cnpj_cpf, @_razao, @_fantasia
WHILE @@FETCH_STATUS = 0
BEGIN
select @_colunas = ''
select @_comando = ''
select @_dataaux = @_dataini
select @_comando = 'INSERT INTO #TEMPSCANN (CNPJ_CPF, RAZAO, FANTASIA) VALUES ( ' + char(39) + @_cnpj_cpf + char(39) +','+ char(39) + @_razao + char(39) +','+ char(39) + @_fantasia + char(39) + ')'
exec(@_comando)
while (@_dataaux <= @_datafim)
begin
select @_colunas = @_colunas + ', [' + convert(varchar(10), @_dataaux, 103) + '] '
select @_dataaux = DATEADD(DAY, 1, @_dataaux)
end
-- Get the next vendor.
FETCH NEXT FROM scann_cursor
INTO @_cnpj_cpf, @_razao, @_fantasia
END
CLOSE scann_cursor;
DEALLOCATE scann_cursor;
select * from #TEMPSCANN
The return of it is:
CNPJ_CPF RAZAO FANTASIA 01/02/2016 02/02/2016 03/02/2016 04/02/2016 05/02/2016 06/02/2016 07/02/2016 08/02/2016 09/02/2016 10/02/2016 11/02/2016 12/02/2016 13/02/2016 14/02/2016 15/02/2016 16/02/2016 17/02/2016 18/02/2016 19/02/2016 20/02/2016 21/02/2016 22/02/2016 23/02/2016 24/02/2016 25/02/2016 26/02/2016 27/02/2016 28/02/2016 29/02/2016
In it I can list every day of the month I want, I would now like to populate this temporary table with the data (QUANTITY on the correct day), how could I end this?