--exec [C6StagingPeriodico].[test_compl_testo_sezione_adeg] 'f', 'BLDMRA59E22G999d', '2015-06-01'
CREATE procedure [C6StagingPeriodico].[test_compl_testo_sezione_adeg]
	@Rete nvarchar(1),
	@CodiceFiscale varchar(16),
	@DTTRIM datetime = null
AS
BEGIN
DECLARE @d nvarchar(8)
set @d = cast(year(@DTTRIM) as nvarchar(4)) + RIGHT('0'+cast(month(@DTTRIM) as nvarchar(2)), 2)
 + RIGHT('0'+cast(day(@DTTRIM) as nvarchar(2)),2)
print @d 
if @DTTRIM is null
begin
if @CodiceFiscale not like 'FF@%' BEGIN
select @d = max(DTTRIM) from 
[C6StagingPeriodico].[WSEIAN2] 
INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA]
ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
WHERE codfis = @CodiceFiscale and RETE = @Rete 
SELECT top(1) ISNULL([TESTO_NOTA],'aa') as testo_nova FROM 
[C6StagingPeriodico].[WSEIAN2] 
INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA]
ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
WHERE codfis  = @CodiceFiscale and RETE = @Rete
and DTTRIM=@d
END
ELSE
BEGIN
DECLARE @CF as nvarchar(max)
set @CF = replace(@CodiceFiscale, 'FF@', '')
select @d = max(DTTRIM) from 
[C6StagingPeriodico].[WSEIAN2] 
INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA]
ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
WHERE codman = @CF
SELECT top(1) ISNULL([TESTO_NOTA],'aa') as testo_nova FROM 
[C6StagingPeriodico].[WSEIAN2] 
INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA]
ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
WHERE  codman = @CF 
and DTTRIM=@d
END
end 
else
begin
if @CodiceFiscale not like 'FF@%' BEGIN
SELECT top(1) ISNULL([TESTO_NOTA],'aa') as testo_nova FROM 
[C6StagingPeriodico].[WSEIAN2] 
INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA]
ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
WHERE codfis  = @CodiceFiscale and RETE = @Rete
and DTTRIM=@DTTRIM
END
ELSE
BEGIN
DECLARE @CFF as nvarchar(max)
set @CFF = replace(@CodiceFiscale, 'FF@', '')
SELECT top(1) ISNULL([TESTO_NOTA],'aa') as testo_nova FROM 
[C6StagingPeriodico].[WSEIAN2] 
INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA]
ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
WHERE  codman = @CFF 
and DTTRIM=@DTTRIM
END
end
END