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.
