none
Como automatizar uma data incremental? RRS feed

  • Pergunta

  • Boa tarde!

    Estou automatizando alguns scripts sql no ssis, porém, são relatórios que são atualizados diariamente...como faço para deixar essa data incremental no ssis?

    Assim, diariamente ele iria executando o script.

    Ricardo.

    quarta-feira, 14 de agosto de 2013 19:37

Respostas

  • Ricardo, fiz as alterações nos valores, de data, teve 2 casos que não entendi qual o criterio que tinha data do mes 06.

    Um dica para facilitar a manutenção de codigos é sempre identar o codigo fica mais facil de visualizar e entender:

    use datacare_ods 
    go
    select 
    	max(loaddate) 
    		from datacare_ods..credit_campaign_provisioning (NOLOCK) 
    		
    select 
    	max(loaddate) from datacare_ods..credit_transaction_notyet (NOLOCK) 
    	
    select 
    	max(loaddate) from datacare_ods..credit_transaction_billing(NOLOCK) 
    	
    select 
    	max(loaddate) from datacare_ods..credit_campaign_percentage (NOLOCK) 
    	
    declare @loaddate_campaign smalldatetime,	 
    		@loaddate_notyet smalldatetime, 
    		@loaddate_faturadas smalldatetime, 
    		@loaddate_percentual smalldatetime, 
    		@reference_period char(7),
    		@MesAnterior char(2)
    		
    set @loaddate_campaign =(select max(loaddate) from datacare_ods..credit_campaign_provisioning(NOLOCK)) 
    set @loaddate_notyet =(select max(loaddate) from datacare_ods..credit_transaction_notyet(NOLOCK)) 
    set @loaddate_faturadas =(select max(loaddate) from datacare_ods..credit_transaction_billing(NOLOCK)) 
    set @loaddate_percentual =(select max(loaddate) from datacare_ods..credit_campaign_percentage(NOLOCK)) 
    set @MesAnterior = datepart(Month, getdate() - 30)
    set @MesAnterior = case when len(@MesAnterior) = 1 then '0' + @MesAnterior else @MesAnterior end
    
    
    set @reference_period = datepart(year, getdate()) + '-' + @MesAnterior 
    
    
    select @loaddate_campaign '@loaddate_campaign' , 
    	   @loaddate_notyet '@loaddate_notyet' , 
    	   @loaddate_faturadas '@loaddate_faturadas' , 
    	   @loaddate_percentual '@loaddate_percentual' , 
    	   @reference_period '@reference_period' , 
    	   @loaddate_faturadas - 1 'referencedate' 
    	   
    DROP Table #fct_customer_campaign 
    
    SELECT * INTO #fct_customer_campaign 
    FROM datacare_ods..credit_campaign_provisioning(NOLOCK) 
    WHERE loaddate = @loaddate_campaign 
    
    DROP TABLE #transacoes_notyed_atendimento 
    SELECT * INTO #transacoes_notyed_atendimento 
    FROM datacare_ods..credit_transaction_notyet(NOLOCK) 
    WHERE loaddate = @loaddate_notyet 
    
    DROP TABLE #transacoes_faturadas_atendimento 
    SELECT * INTO #transacoes_faturadas_atendimento 
    FROM datacare_ods..credit_transaction_billing(nolock) 
    WHERE loaddate between GETDATE() - 30 and @loaddate_faturadas 
    
    CREATE INDEX idx_bill ON #transacoes_faturadas_atendimento(BillRuleID) 
    CREATE INDEX idx_bill ON #transacoes_notyed_atendimento(BillRuleID) 
    CREATE INDEX idx_bill ON #fct_customer_campaign(BillRuleID) 
    CREATE INDEX idx_cust ON #transacoes_faturadas_atendimento(CustomerID) 
    CREATE INDEX idx_cust ON #transacoes_notyed_atendimento(CustomerID) 
    CREATE INDEX idx_cust ON #fct_customer_campaign(CustomerID) 
    CREATE INDEX idx_tra ON #transacoes_faturadas_atendimento(TransactionID) 
    CREATE INDEX idx_tra ON #transacoes_notyed_atendimento(TransactionID) 
    CREATE INDEX idx_camp ON #fct_customer_campaign(CampaignID) 
    
    DROP TABLE #CampanhaTelecinejunho2013 
    
    SELECT * INTO #CampanhaTelecinejunho2013 
    FROM #transacoes_faturadas_atendimento a 
    WHERE SMSAccount = '553562' 
    and CreateDate >= '2013-06-23' -- QUAL O CRITERIO ?
    and exists( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b 
    				WHERE a.CustomerID = b.CustomerID and Oferta = 'Telecine 3' and Falha = 'N' ) 
    				
    CREATE INDEX idx_tra ON #CampanhaTelecinejunho2013(TransactionID) 
    
    SELECT 
    	convert(varchar(7), a.BillingDate, 120) as [Ms Faturamento] , 
    	cast(sum(a.grossamount) as money) as [Total Faturado] 
    		FROM #transacoes_faturadas_atendimento a 
    WHERE a.loaddate between GETDATE() - 30 and @loaddate_faturadas 
    and convert(char(7),BillingDate, 120) = @MesAnterior 
    and not exists( select transactionID 
    					from #CampanhaTelecinejunho2013 b where a.TransactionId = b.TransactionID ) 
    				GROUP BY convert (varchar(7), BillingDate, 120) 
    				ORDER BY 1 
    				
    DROP TABLE #CampanhaTelecinejunho2013NotYet 
    
    SELECT * INTO #CampanhaTelecinejunho2013NotYet 
    FROM #transacoes_notyed_atendimento a 
    WHERE SMSAccount = '553562' 
    and CreateDate >= '2013-06-23' -- QUAL O CRITERIO ?
    and exists( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b 
    				WHERE a.CustomerID = b.CustomerID 
    				and Oferta = 'Telecine 3' and Falha = 'N' ) 
    				
    CREATE INDEX idx_tra ON #CampanhaTelecinejunho2013NotYet(TransactionID) 
    
    SELECT 
    	convert(varchar(7), a.EstimatedBillingDate, 120) as [Ms Faturamento] , 
    	cast(sum(a.grossamount) as money) as [Total No faturado] 
    		FROM #transacoes_notyed_atendimento a 
    		INNER JOIN cust_subscription cs ON a .CustomerId = cs.CustomerId and PackagestatusID <> 'C' 
    WHERE convert(varchar(7), a.EstimatedBillingDate, 120) >= @MesAnterior 
    and a.loaddate = @loaddate_notyet 
    and not exists( select transactionID from #CampanhaTelecinejunho2013NotYet b 
    				where a.TransactionID = b.TransactionID ) 
    GROUP BY convert (varchar(7), EstimatedBillingDate, 120) 
    ORDER BY 1 
    
    DROP TABLE #Telecinejunho2013Campaign 
    
    SELECT * INTO #Telecinejunho2013Campaign 
    FROM #fct_customer_campaign a 
    WHERE CampaignID = '3606' 
    AND isAtendimento = 'Y' 
    and exists ( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b 
    				WHERE a.CustomerID = b.CustomerID and Oferta = 'Telecine 3' and Falha = 'N' ) 
    				
    SELECT 
    	convert(varchar(7), EstimatedBillingDate, 120) as [Ms Faturamento] , 
    	cast(sum(price) as money) as [Total Campanhas] 
    		FROM #fct_customer_campaign a (NOLOCK) 
    WHERE convert(varchar(7), EstimatedBillingDate, 120) >= @MesAnterior 
    AND loaddate = @loaddate_campaign 
    AND isAtendimento = 'Y' 
    AND NOT EXISTS( SELECT 1 FROM #transacoes_notyed_atendimento b (NOLOCK) 
    					WHERE a.customerid = b.customerid 
    					and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), b.EstimatedBillingDate, 120) 
    					and a.billruleid = b.billruleid 
    					and loaddate = @loaddate_notyet ) 
    AND NOT EXISTS( SELECT 1 FROM #transacoes_faturadas_atendimento c (NOLOCK) 
    					WHERE a.customerid = c.customerid 
    					and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), c.BillingDate, 120) 
    					and a.billruleid = c.billruleid 
    					and loaddate = @loaddate_faturadas ) 
    GROUP BY convert (varchar(7), EstimatedBillingDate, 120) ORDER BY 1 
    
    SELECT 
    	CONVERT (varchar(7), loadDate, 120) as [Ms Faturamento] , 
    	CAST(SUM(discounttotal) as money) as [TotalDescontoPercentual] 
    		FROM datacare_ods .. credit_campaign_percentage 
    WHERE loadDate = @loaddate_percentual 
    GROUP BY convert (varchar(7), loadDate, 120) ORDER BY 1 
    
    SELECT 
    	convert (char(7),OfferDate,120) OfferPeriod , 
    	Status , 
    	count(1) Quantidade 
    		from cust_offer co 
    		INNER JOIN negociacao_oferta no ON co . offerid = no.nof_id 
    WHERE Status IN(1,0,4) 
    and OfferDate >= convert( char (7), dateadd(MM,-1,getdate() - 1),120) +'-01' 
    and OfferDate < convert( char (7), getdate() - 1,120) +'-01' 
    and nto_id = 4 
    GROUP BY convert (char(7),OfferDate,120) , Status order by 1,2


    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 15 de agosto de 2013 20:49

Todas as Respostas

  • Tenho alguns pacotes parecidos com o que voce diz, por exemplo tenho um pacote que tem que "pegar" informações do banco dos ultimos 5 dias e fazer algumas transformações, o que faço é em um dos primeiros passos do pacote pegar estas informações e jogar em uma temporaria para trabalhar estes dados, isto faço utilizando onde minha data for maior do que o ultimo quinto dia por exemplo: "Data >= getdate() - 5", existem varias funcções de datas que voce pode utilizar como o getdate(), datepart, dateadd, etc.

    Alexandre Matayosi Conde Mauricio.

    quarta-feira, 14 de agosto de 2013 19:48
  • entendi, valeu pela dica, mas deixa eu ver se entendi direito...antes da fase de execução do script eu crio uma tabela temp e jogo todas as informações nela, depois crio um "filtro" para buscar sempre uma data maior que a do último dia de atualização e schedulo para rodar todos os dias, certo?

    Ricardo.

    quarta-feira, 14 de agosto de 2013 19:55
  • Isso mesmo, ou então na sua propria execução do script voce coloca o filtro de data dinamicamente como citei aplicando a sua regra, eu particularmente prefiro trabalhar como temporarias mas tambem poderia ser o filtro na sua execução direto.


    Alexandre Matayosi Conde Mauricio.

    quarta-feira, 14 de agosto de 2013 19:59
  • entendi, valeu pela dica, mas deixa eu ver se entendi direito...antes da fase de execução do script eu crio uma tabela temp e jogo todas as informações nela, depois crio um "filtro" para buscar sempre uma data maior que a do último dia de atualização e schedulo para rodar todos os dias, certo?

    Ricardo.

    O pessoal vinha rodando na mão bloco a bloco e agora eu já criei um job para executar automático, porém, alguns campos de data devem ser atualizados diariamente na mão, essas datas que eu quero que já sejam atualizadas incrementalmente dia a dia aumentando sempre um dia com relação à última atualização.

    quarta-feira, 14 de agosto de 2013 20:02
  • Parece simples, o que eu faria seria cadastrar em alguma tabela a data da ultima atualização, cada vez que a rotina rodar pegaria a data da ultima atualização e adicionaria 1 dia (getdate() + 1) e utilizaria este campo como filtro.

    Alexandre Matayosi Conde Mauricio.

    quarta-feira, 14 de agosto de 2013 20:04
  • Ok, vou testar isso então...abraço.
    quarta-feira, 14 de agosto de 2013 20:06
  • Ok, depois poste o resultado e se resolver não esqueça de marcar as respostas que te ajudaram.

    Alexandre Matayosi Conde Mauricio.

    quarta-feira, 14 de agosto de 2013 21:25
  • opa...pode deixar, vou fazer os testes hj.
    quinta-feira, 15 de agosto de 2013 13:06
  • alexandre, vc tem algum e-mail? assim eu te mando o script e vc me diz qual forma acha melhor, criando uma variavel ou outra forma.
    quinta-feira, 15 de agosto de 2013 13:58
  • Ricardo, tenho sim, porem no meu email pessoal na empresa é bloqueado e só vejo a noite ou no celular (o que é muito dificil para ler codigos e responder no forum), no email da empresa tambem só recebo determinados emails externos, emails como gmail, hotmail,etc são bloqueados pelo AD.

    Para garantir a privacidade das suas informações aqui, voce pode postar dados ficticios somente com a estrutura parecida e a logica sem problemas.


    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 15 de agosto de 2013 14:10
  • então...o meu e-mail é corporativo, será q vc consegue receber?
    quinta-feira, 15 de agosto de 2013 14:23
  • pode tentar: alexandre.matayosi@niteo.com.br

    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 15 de agosto de 2013 15:11
  • Não deu certo, voltou...vc está nessa empresa na berrine ou em moema?...estou no prédio da d e d aki na berrini...mas então, algumas datas são mês e ano e outras dia, mês e ano. Como eu crio os comandos getdate()+1? Poderia mostrar pelo menos um exemplo de cada? Um com me e ano e outro com dia, mês e ano? Outra coisa, como eu faço se foe ao contrário, tipo nos EUA, ano, mês e dia ou ano e mês?

    Valeu, abraço.

    quinta-feira, 15 de agosto de 2013 15:43
  • Então, no momento em nenhuma das duas, estou alocado em cliente, mas vamos la...tanto se for a data no padrão brasileiro ou americano não importa para o SQL pois as funcões de data reconhecem qual o padrão que esta sendo utilizado pelo SQL Server.

    Para te dar um exemplo somente de mes e ano preciso saber qual o tipo de dados desta sua coluna, para tipo de dados de data completo como o datetime por exemplo voce pode se basear na estrutura abaixo, onde crio uma tabela temporaria e insiro registros com datas dos ultimos 10 dias, depois utilizo um filtro para capturar somente os ultimos 5 dias, que aparentemente é o filtro que voce queria no inicio, de uma olhada:

    -- Criando tabela temporaria
    Create table #teste (id int identity(1,1), data datetime)
    
    -- Inserindo dados
    insert into #teste
    values (getdate()),
    (getdate() - 1),
    (getdate() - 2),
    (getdate() - 3),
    (getdate() - 4),
    (getdate() - 5),
    (getdate() - 6),
    (getdate() - 7),
    (getdate() - 8),
    (getdate()- 9)
    
    -- visualizando toda a tabela
    select * from #teste
    
    -- exemplo de filtro dinamico que captura sempre informações dos ultimos 5 dias
    select * from #teste teste
    where data >= GETDATE() - 5
    


    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 15 de agosto de 2013 15:54
  • Então, legal o exemplo, usei aki e deu certo..mas imagina a seguinte cena: eu tenho um script que atualiza dados de uma tabela "diariamente", mas o resultado desse atualização nunca será o mesmo, pois são muitos clientes e, uns pagam, outros não, outros pagam adiantado, outros atrasado..então a chance do valor ser igual de um dia pro outro seria de menos de 0,00000000000000000000001%...mas então...eu rodo na mão hoje 15/08/2013...amanhã quria que fosse automatizado pra que eu não precise mais ir no script, mude a data na mão e rode no f5....mas pra isso, o script teria que ter um comando me dizendo que, a atualização diária seja feita até o dia de amanhã e não mais só até hoje...e mais um duro detalhe, tenho vários campos no script que colocamos a data na mão, acho que uns 5..e, desses 5, uns 2 são mês/ano e os outros dia/mês/ano...como poderia resolver isso?

    Valeu.

    Abraço.

    quinta-feira, 15 de agosto de 2013 17:01
  • No caso que voce disse hoje voce rodou como 15/08/13 e amanhã seria 16/08/13, certo ? se for isso voce pode usar a data = getdate()

    Sobre a parte que tem somente mes/ano voce pode utilizar a função datepart para pegar o mes e o ano, por exemplo "select datepart(day, getdate())", se voce rodar esse select vai retornar o dia do getdate() que é a data completa porem vai trazer somente o dia, se voce trocar o day por month vai trazer o mes, voce pode ver qual o formato da sua data e usar uma concatenação.


    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 15 de agosto de 2013 17:27
  • Puxa cara, não consegui...vê se pode ajudar com o script mesmo...como se estivesse explicando para uma criancinha, por favor...kkk

    use datacare_ods select max(loaddate) from datacare_ods..credit_campaign_provisioning(NOLOCK) select max(loaddate) from datacare_ods..credit_transaction_notyet(NOLOCK) select max(loaddate) from datacare_ods..credit_transaction_billing(NOLOCK) select max(loaddate) from datacare_ods..credit_campaign_percentage(NOLOCK) declare @loaddate_campaign smalldatetime, @loaddate_notyet smalldatetime, @loaddate_faturadas smalldatetime, @loaddate_percentual smalldatetime, @reference_period char(7) set @loaddate_campaign =(select max(loaddate) from datacare_ods..credit_campaign_provisioning(NOLOCK)) set @loaddate_notyet =(select max(loaddate) from datacare_ods..credit_transaction_notyet(NOLOCK)) set @loaddate_faturadas =(select max(loaddate) from datacare_ods..credit_transaction_billing(NOLOCK)) set @loaddate_percentual =(select max(loaddate) from datacare_ods..credit_campaign_percentage(NOLOCK)) set @reference_period = '2013-07' select @loaddate_campaign '@loaddate_campaign' , @loaddate_notyet '@loaddate_notyet' , @loaddate_faturadas '@loaddate_faturadas' , @loaddate_percentual '@loaddate_percentual' , @reference_period '@reference_period' , @loaddate_faturadas - 1 'referencedate' DROP Table #fct_customer_campaign SELECT * INTO #fct_customer_campaign FROM datacare_ods..credit_campaign_provisioning(NOLOCK) WHERE loaddate = @loaddate_campaign DROP TABLE #transacoes_notyed_atendimento SELECT * INTO #transacoes_notyed_atendimento FROM datacare_ods..credit_transaction_notyet(NOLOCK) WHERE loaddate = @loaddate_notyet DROP TABLE #transacoes_faturadas_atendimento SELECT * INTO #transacoes_faturadas_atendimento FROM datacare_ods..credit_transaction_billing(nolock) WHERE loaddate between '2013-07-14' and @loaddate_faturadas CREATE INDEX idx_bill ON #transacoes_faturadas_atendimento(BillRuleID) CREATE INDEX idx_bill ON #transacoes_notyed_atendimento(BillRuleID) CREATE INDEX idx_bill ON #fct_customer_campaign(BillRuleID) CREATE INDEX idx_cust ON #transacoes_faturadas_atendimento(CustomerID) CREATE INDEX idx_cust ON #transacoes_notyed_atendimento(CustomerID) CREATE INDEX idx_cust ON #fct_customer_campaign(CustomerID) CREATE INDEX idx_tra ON #transacoes_faturadas_atendimento(TransactionID) CREATE INDEX idx_tra ON #transacoes_notyed_atendimento(TransactionID) CREATE INDEX idx_camp ON #fct_customer_campaign(CampaignID) DROP TABLE #CampanhaTelecinejunho2013 SELECT * INTO #CampanhaTelecinejunho2013 FROM #transacoes_faturadas_atendimento a WHERE SMSAccount = '553562' and CreateDate >= '2013-06-23' and exists( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b WHERE a.CustomerID = b.CustomerID and Oferta = 'Telecine 3' and Falha = 'N' ) CREATE INDEX idx_tra ON #CampanhaTelecinejunho2013(TransactionID) SELECT convert(varchar(7), a.BillingDate, 120) as [Ms Faturamento] , cast(sum(a.grossamount) as money) as [Total Faturado] FROM #transacoes_faturadas_atendimento a WHERE a.loaddate between '2013-07-14' and @loaddate_faturadas and convert(char(7),BillingDate, 120) = '2013-07' and not exists( select transactionID from #CampanhaTelecinejunho2013 b where a.TransactionId = b.TransactionID ) GROUP BY convert (varchar(7), BillingDate, 120) ORDER BY 1 DROP TABLE #CampanhaTelecinejunho2013NotYet SELECT * INTO #CampanhaTelecinejunho2013NotYet FROM #transacoes_notyed_atendimento a WHERE SMSAccount = '553562' and CreateDate >= '2013-06-23' and exists( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b WHERE a.CustomerID = b.CustomerID and Oferta = 'Telecine 3' and Falha = 'N' ) CREATE INDEX idx_tra ON #CampanhaTelecinejunho2013NotYet(TransactionID) SELECT convert(varchar(7), a.EstimatedBillingDate, 120) as [Ms Faturamento] , cast(sum(a.grossamount) as money) as [Total No faturado] FROM #transacoes_notyed_atendimento a INNER JOIN cust_subscription cs ON a .CustomerId = cs.CustomerId and PackagestatusID <> 'C' WHERE convert(varchar(7), a.EstimatedBillingDate, 120) >= '2013-07' and a.loaddate = @loaddate_notyet and not exists( select transactionID from #CampanhaTelecinejunho2013NotYet b where a.TransactionID = b.TransactionID ) GROUP BY convert (varchar(7), EstimatedBillingDate, 120) ORDER BY 1 DROP TABLE #Telecinejunho2013Campaign SELECT * INTO #Telecinejunho2013Campaign FROM #fct_customer_campaign a WHERE CampaignID = '3606' AND isAtendimento = 'Y' and exists ( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b WHERE a.CustomerID = b.CustomerID and Oferta = 'Telecine 3' and Falha = 'N' ) SELECT convert(varchar(7), EstimatedBillingDate, 120) as [Ms Faturamento] , cast(sum(price) as money) as [Total Campanhas] FROM #fct_customer_campaign a (NOLOCK) WHERE convert(varchar(7), EstimatedBillingDate, 120) >= '2013-07' AND loaddate = @loaddate_campaign AND isAtendimento = 'Y' AND NOT EXISTS( SELECT 1 FROM #transacoes_notyed_atendimento b (NOLOCK) WHERE a.customerid = b.customerid and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), b.EstimatedBillingDate, 120) and a.billruleid = b.billruleid and loaddate = @loaddate_notyet ) AND NOT EXISTS( SELECT 1 FROM #transacoes_faturadas_atendimento c (NOLOCK) WHERE a.customerid = c.customerid and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), c.BillingDate, 120) and a.billruleid = c.billruleid and loaddate = @loaddate_faturadas ) GROUP BY convert (varchar(7), EstimatedBillingDate, 120) ORDER BY 1 SELECT CONVERT (varchar(7), loadDate, 120) as [Ms Faturamento] , CAST(SUM(discounttotal) as money) as [TotalDescontoPercentual] FROM datacare_ods .. credit_campaign_percentage WHERE loadDate = @loaddate_percentual GROUP BY convert (varchar(7), loadDate, 120) ORDER BY 1 SELECT convert (char(7),OfferDate,120) OfferPeriod , Status , count(1) Quantidade from cust_offer co INNER JOIN negociacao_oferta no ON co . offerid = no.nof_id WHERE Status IN(1,0,4) and OfferDate >= convert( char (7), dateadd(MM,-1,getdate() - 1),120) +'-01' and OfferDate < convert( char (7), getdate() - 1,120) +'-01' and nto_id = 4 GROUP BY convert (char(7),OfferDate,120) , Status order by 1,2
    quinta-feira, 15 de agosto de 2013 20:04
  • Ricardo, fiz as alterações nos valores, de data, teve 2 casos que não entendi qual o criterio que tinha data do mes 06.

    Um dica para facilitar a manutenção de codigos é sempre identar o codigo fica mais facil de visualizar e entender:

    use datacare_ods 
    go
    select 
    	max(loaddate) 
    		from datacare_ods..credit_campaign_provisioning (NOLOCK) 
    		
    select 
    	max(loaddate) from datacare_ods..credit_transaction_notyet (NOLOCK) 
    	
    select 
    	max(loaddate) from datacare_ods..credit_transaction_billing(NOLOCK) 
    	
    select 
    	max(loaddate) from datacare_ods..credit_campaign_percentage (NOLOCK) 
    	
    declare @loaddate_campaign smalldatetime,	 
    		@loaddate_notyet smalldatetime, 
    		@loaddate_faturadas smalldatetime, 
    		@loaddate_percentual smalldatetime, 
    		@reference_period char(7),
    		@MesAnterior char(2)
    		
    set @loaddate_campaign =(select max(loaddate) from datacare_ods..credit_campaign_provisioning(NOLOCK)) 
    set @loaddate_notyet =(select max(loaddate) from datacare_ods..credit_transaction_notyet(NOLOCK)) 
    set @loaddate_faturadas =(select max(loaddate) from datacare_ods..credit_transaction_billing(NOLOCK)) 
    set @loaddate_percentual =(select max(loaddate) from datacare_ods..credit_campaign_percentage(NOLOCK)) 
    set @MesAnterior = datepart(Month, getdate() - 30)
    set @MesAnterior = case when len(@MesAnterior) = 1 then '0' + @MesAnterior else @MesAnterior end
    
    
    set @reference_period = datepart(year, getdate()) + '-' + @MesAnterior 
    
    
    select @loaddate_campaign '@loaddate_campaign' , 
    	   @loaddate_notyet '@loaddate_notyet' , 
    	   @loaddate_faturadas '@loaddate_faturadas' , 
    	   @loaddate_percentual '@loaddate_percentual' , 
    	   @reference_period '@reference_period' , 
    	   @loaddate_faturadas - 1 'referencedate' 
    	   
    DROP Table #fct_customer_campaign 
    
    SELECT * INTO #fct_customer_campaign 
    FROM datacare_ods..credit_campaign_provisioning(NOLOCK) 
    WHERE loaddate = @loaddate_campaign 
    
    DROP TABLE #transacoes_notyed_atendimento 
    SELECT * INTO #transacoes_notyed_atendimento 
    FROM datacare_ods..credit_transaction_notyet(NOLOCK) 
    WHERE loaddate = @loaddate_notyet 
    
    DROP TABLE #transacoes_faturadas_atendimento 
    SELECT * INTO #transacoes_faturadas_atendimento 
    FROM datacare_ods..credit_transaction_billing(nolock) 
    WHERE loaddate between GETDATE() - 30 and @loaddate_faturadas 
    
    CREATE INDEX idx_bill ON #transacoes_faturadas_atendimento(BillRuleID) 
    CREATE INDEX idx_bill ON #transacoes_notyed_atendimento(BillRuleID) 
    CREATE INDEX idx_bill ON #fct_customer_campaign(BillRuleID) 
    CREATE INDEX idx_cust ON #transacoes_faturadas_atendimento(CustomerID) 
    CREATE INDEX idx_cust ON #transacoes_notyed_atendimento(CustomerID) 
    CREATE INDEX idx_cust ON #fct_customer_campaign(CustomerID) 
    CREATE INDEX idx_tra ON #transacoes_faturadas_atendimento(TransactionID) 
    CREATE INDEX idx_tra ON #transacoes_notyed_atendimento(TransactionID) 
    CREATE INDEX idx_camp ON #fct_customer_campaign(CampaignID) 
    
    DROP TABLE #CampanhaTelecinejunho2013 
    
    SELECT * INTO #CampanhaTelecinejunho2013 
    FROM #transacoes_faturadas_atendimento a 
    WHERE SMSAccount = '553562' 
    and CreateDate >= '2013-06-23' -- QUAL O CRITERIO ?
    and exists( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b 
    				WHERE a.CustomerID = b.CustomerID and Oferta = 'Telecine 3' and Falha = 'N' ) 
    				
    CREATE INDEX idx_tra ON #CampanhaTelecinejunho2013(TransactionID) 
    
    SELECT 
    	convert(varchar(7), a.BillingDate, 120) as [Ms Faturamento] , 
    	cast(sum(a.grossamount) as money) as [Total Faturado] 
    		FROM #transacoes_faturadas_atendimento a 
    WHERE a.loaddate between GETDATE() - 30 and @loaddate_faturadas 
    and convert(char(7),BillingDate, 120) = @MesAnterior 
    and not exists( select transactionID 
    					from #CampanhaTelecinejunho2013 b where a.TransactionId = b.TransactionID ) 
    				GROUP BY convert (varchar(7), BillingDate, 120) 
    				ORDER BY 1 
    				
    DROP TABLE #CampanhaTelecinejunho2013NotYet 
    
    SELECT * INTO #CampanhaTelecinejunho2013NotYet 
    FROM #transacoes_notyed_atendimento a 
    WHERE SMSAccount = '553562' 
    and CreateDate >= '2013-06-23' -- QUAL O CRITERIO ?
    and exists( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b 
    				WHERE a.CustomerID = b.CustomerID 
    				and Oferta = 'Telecine 3' and Falha = 'N' ) 
    				
    CREATE INDEX idx_tra ON #CampanhaTelecinejunho2013NotYet(TransactionID) 
    
    SELECT 
    	convert(varchar(7), a.EstimatedBillingDate, 120) as [Ms Faturamento] , 
    	cast(sum(a.grossamount) as money) as [Total No faturado] 
    		FROM #transacoes_notyed_atendimento a 
    		INNER JOIN cust_subscription cs ON a .CustomerId = cs.CustomerId and PackagestatusID <> 'C' 
    WHERE convert(varchar(7), a.EstimatedBillingDate, 120) >= @MesAnterior 
    and a.loaddate = @loaddate_notyet 
    and not exists( select transactionID from #CampanhaTelecinejunho2013NotYet b 
    				where a.TransactionID = b.TransactionID ) 
    GROUP BY convert (varchar(7), EstimatedBillingDate, 120) 
    ORDER BY 1 
    
    DROP TABLE #Telecinejunho2013Campaign 
    
    SELECT * INTO #Telecinejunho2013Campaign 
    FROM #fct_customer_campaign a 
    WHERE CampaignID = '3606' 
    AND isAtendimento = 'Y' 
    and exists ( SELECT customerID FROM dbmii.[directv\etsonoha].CampanhaProdutos20130621 b 
    				WHERE a.CustomerID = b.CustomerID and Oferta = 'Telecine 3' and Falha = 'N' ) 
    				
    SELECT 
    	convert(varchar(7), EstimatedBillingDate, 120) as [Ms Faturamento] , 
    	cast(sum(price) as money) as [Total Campanhas] 
    		FROM #fct_customer_campaign a (NOLOCK) 
    WHERE convert(varchar(7), EstimatedBillingDate, 120) >= @MesAnterior 
    AND loaddate = @loaddate_campaign 
    AND isAtendimento = 'Y' 
    AND NOT EXISTS( SELECT 1 FROM #transacoes_notyed_atendimento b (NOLOCK) 
    					WHERE a.customerid = b.customerid 
    					and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), b.EstimatedBillingDate, 120) 
    					and a.billruleid = b.billruleid 
    					and loaddate = @loaddate_notyet ) 
    AND NOT EXISTS( SELECT 1 FROM #transacoes_faturadas_atendimento c (NOLOCK) 
    					WHERE a.customerid = c.customerid 
    					and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), c.BillingDate, 120) 
    					and a.billruleid = c.billruleid 
    					and loaddate = @loaddate_faturadas ) 
    GROUP BY convert (varchar(7), EstimatedBillingDate, 120) ORDER BY 1 
    
    SELECT 
    	CONVERT (varchar(7), loadDate, 120) as [Ms Faturamento] , 
    	CAST(SUM(discounttotal) as money) as [TotalDescontoPercentual] 
    		FROM datacare_ods .. credit_campaign_percentage 
    WHERE loadDate = @loaddate_percentual 
    GROUP BY convert (varchar(7), loadDate, 120) ORDER BY 1 
    
    SELECT 
    	convert (char(7),OfferDate,120) OfferPeriod , 
    	Status , 
    	count(1) Quantidade 
    		from cust_offer co 
    		INNER JOIN negociacao_oferta no ON co . offerid = no.nof_id 
    WHERE Status IN(1,0,4) 
    and OfferDate >= convert( char (7), dateadd(MM,-1,getdate() - 1),120) +'-01' 
    and OfferDate < convert( char (7), getdate() - 1,120) +'-01' 
    and nto_id = 4 
    GROUP BY convert (char(7),OfferDate,120) , Status order by 1,2


    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 15 de agosto de 2013 20:49