-- =============================================
-- Author:		alessandro Tringali
-- Create date: 06 luglio 2010
-- Description:	estrae la lista dei clienti per fase II
-- =============================================
-- [C6MartPeriodico].[SRV_LISTA_CLIENTI_FASE2] '20110301', '20101231'
CREATE procedure [C6MartPeriodico].[SRV_LISTA_CLIENTI_FASE2]
	@DTTRIM int ,
    @DTTRIM_PREC DATETIME
AS
BEGIN
select
C6.Rete, 
C6.Cod_Agente, 
C6.Cod_Fiscale, 
C6.CodiceContratto, 
C6.Freq_Monitor, C6.Freq_Diagnosi, data_perf, 
pdf.datagenerazione,
data_fineavanzato as data_revoca, 
case when w.codfis is null then 0 else 1 end as flg_monit
FROM                  C6Mart.CONTRATTOSEI AS C6 INNER JOIN
                      C6Mart.GESTIONE_PDF_FTP AS PDF 
                      ON C6.Rete = PDF.Rete 
                       AND C6.Cod_Fiscale = PDF.CodiceFiscale 
                       AND   C6.CodiceContratto = SUBSTRING(PDF.NomeFile, 1, 11)
                       left outer join 
                       (select distinct codfis, rete from c6stagingperiodico.wanab w where dtfine='99991231' and dtrif <= CAST(@DTTRIM AS int) ) w
                       on  w.codfis=c6.cod_fiscale
                       and w.rete=c6.rete
WHERE     (1 = 1) 
                      AND (ISNULL(C6.Data_FineAvanzato, CAST('99991231' AS datetime)) > CAST(@DTTRIM_PREC AS datetime)) 
                      AND (PDF.DataGenerazione < '20110601') 
                      AND c6.INVIOBENVENUTO='S' 
					--AND isnull(C6.Data_Invio_Monitoraggio_Trimestrale,cast('20091231' as datetime)) < cast(C6MartPeriodico.getTrimestre(GETDATE(), 0) as datetime)
END