SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;


Declare @F_BANK_ID int = [[bank_id]];
Declare @dday int = [[cur_dt]];
DECLARE @cnt_month_with_trns int = 4;
DECLARE @cnt_month_max_kpi int = 12;
DECLARE @min_PAmountCHF numeric(15,2) = 10000;
DECLARE @min_EAmountCHF numeric(15,2) = 50000;
DECLARE @TW int = 2;

DECLARE @dt date;
set @dt = try_convert(date,cast(@dday as varchar),112);
if @dt is NULL
	set @dt = cast(sysdatetime() as date);

DECLARE @NamMonth int, --previous month
		@NamMonth2 int; --@cnt_month_max_kpi months later
set @NamMonth = cast(convert(varchar,dateadd(month,-1,@dt),112) as int);
set @NamMonth = (@NamMonth-(@NamMonth % 100))/100;
set @NamMonth2 = cast(convert(varchar,dateadd(month,-@cnt_month_max_kpi,@dt),112) as int);
set @NamMonth2 = (@NamMonth2-(@NamMonth2 % 100))/100;

declare @N_CP int;
declare @N_day int;
select @N_CP = CounterpartyCountThreshold,
	@N_day = DaysThreshold 
from (select top 1 * from CCR.dbo.CounterpartyStatParams_Wuestenrot where UserBank_id=@F_BANK_ID) s;

with
	links0 as
		(select * from FAF.FA_LINKS 
		where UserBank_id=@F_BANK_ID 
			and isnull(LinkOpenDate,convert(date,'19010101',112))<=@dt 
			and isnull(LinkExpiryDate,convert(date,'20991231',112))>=@dt),
	-- bank-specific variables definition
	mcntrp as 
		(select UserBank_id,Customer_id,count(*) as cnt from 
			(select distinct grp.UserBank_id,grp.Customer_id,tr.CounterpartyIBAN 
			from 
				FAF.FA_TRANSACTIONS tr
			join
				FAF.FA_CUSTOMERS_GROUPS grp
			on tr.UserBank_id=grp.UserBank_id and tr.CustomerGroup_id=grp.CustomerGroup_id 	
			where tr.UserBank_id=@F_BANK_ID
				and tr.TransactionDate<=@dt
				and tr.TransactionDate>=dateadd(day,-29,@dt)
				and tr.InflowOutflow='I') tr_in
		group by UserBank_id,Customer_id),
	tr_out as
		(select distinct grp.UserBank_id,grp.Customer_id,tr.CounterpartyIBAN 
		from 
			FAF.FA_TRANSACTIONS tr
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on tr.UserBank_id=grp.UserBank_id and tr.CustomerGroup_id=grp.CustomerGroup_id 	
		where tr.UserBank_id=@F_BANK_ID
			and tr.TransactionDate<=@dt
			and tr.TransactionDate>dateadd(day,-@N_day,@dt)
			and tr.InflowOutflow='O'),
	coll as
		(select t0.UserBank_id,t0.Customer_id,
			cast(substring(string_agg(convert(varchar(max),cast(t0.CounterpartyIBAN as varchar(4000))),',') within group (order by t0.CounterpartyIBAN),1,4000) as varchar(4000)) as CPCollector_LIST
		from 
			tr_out t0
		join
			(select UserBank_id,CounterpartyIBAN from tr_out t1 group by UserBank_id,CounterpartyIBAN having count(*)>=@N_CP) cp
		on cp.UserBank_id=t0.UserBank_id and cp.CounterpartyIBAN=t0.CounterpartyIBAN
		group by t0.UserBank_id,t0.Customer_id),
	accl as
		(select grp.UserBank_id,grp.Customer_id,trans.Account_id,trans.SHORT_KT_AMOUNT,trans.SHORT_KT_DURATION,
			row_number() over (partition by grp.UserBank_id,grp.Customer_id order by trans.SHORT_KT_AMOUNT desc,trans.SHORT_KT_DURATION,trans.Account_id) as rn
		from 
			FAF.FA_CUSTOMERS_GROUPS grp
		join
			(select ac.UserBank_id,ac.Account_id,ac.CustomerGroup_id,
				sum(tr.AmountLocalCurrency) as SHORT_KT_AMOUNT,
				max(datediff(day,ac.AccountOpenDate,ac.AccountCloseDate)) as SHORT_KT_DURATION
			from
				FAF.FA_ACCOUNTS ac
			join
				FAF.FA_TRANSACTIONS tr
			on tr.UserBank_id=ac.UserBank_id and tr.Account_id=ac.Account_id
			where ac.UserBank_id=@F_BANK_ID and ac.AccountCloseDate>=dateadd(day,-2,@dt)
				and datediff(day,ac.AccountOpenDate,ac.AccountCloseDate)<=89
			group by ac.UserBank_id,ac.Account_id,ac.CustomerGroup_id) trans 
		on trans.UserBank_id=grp.UserBank_id and trans.CustomerGroup_id=grp.CustomerGroup_id),
	sld_all as
		(select kt.UserBank_id,grp.Customer_id,kt.Account_id,kts.SaldoDate,grp.Partner_code,kt.AccountOpenDate,kt.AccountType_cd,
			kt.AccountCloseDate,case when kts.SaldoLocal>0 then kts.SaldoLocal else 0 end as SaldoLocal,act.[Group]
		from 
			FAF.FA_ACCOUNTS kt
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=kt.UserBank_id and grp.CustomerGroup_id=kt.CustomerGroup_id
		left join
			FAF.FA_SALDO_HISTORY kts
		on kt.UserBank_id=kts.UserBank_id and kt.Account_id=kts.Account_id
		left join
			FAF.AccountTypes_Wuestenrot act
		on kt.AccountType_cd=act.AccountType_cd),
	nl as
		(select distinct UserBank_id,CustomerA_id from FAF.FA_LINKS where LinkOpenDate<=@dt and LinkOpenDate>=dateadd(day,-2,@dt)),
	ns as
		(select distinct UserBank_id,Customer_id from FAF.FA_SIGNATORIES where SignatoryOpenDate<=@dt and SignatoryOpenDate>=dateadd(day,-2,@dt)),
	pepr as
		(select distinct links0.UserBank_id,links0.CustomerA_id 
		from
			links0
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=links0.UserBank_id and grp.CustomerGroup_id=links0.CustomerGroupB_id
		join
			FAF.FA_CUSTOMERS kd
		on kd.UserBank_id=grp.UserBank_id and kd.Customer_id=grp.Customer_id
		where links0.CustomerA_id<>kd.Customer_id and isnull(kd.CustomerPepStatus_cd,'0')<>'0'),
	acsc as
		(select distinct kt.UserBank_id,grp.Customer_id,kt.AccountSourceOfFunds_cd
		from 
			FAF.FA_ACCOUNTS kt
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=kt.UserBank_id and grp.CustomerGroup_id=kt.CustomerGroup_id
		where kt.AccountStatus_cd in ('A','G')),
	prdcd as
		(select distinct kt.UserBank_id,grp.Customer_id,kt.ProductCode_cd,prcd.Description as ProductCode
		from 
			FAF.FA_ACCOUNTS kt
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=kt.UserBank_id and grp.CustomerGroup_id=kt.CustomerGroup_id
		left join
			FAF.FA_RT_ProductCode prcd
		on prcd.UserBank_id=kt.UserBank_id and prcd.ProductCode_cd=kt.ProductCode_cd
		where kt.AccountCloseDate is NULL or kt.AccountCloseDate>@dt),
	acntp as
		(select distinct kt.UserBank_id,grp.Customer_id,kt.AccountType_cd,actp.Description as AccountType
		from 
			FAF.FA_ACCOUNTS kt
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=kt.UserBank_id and grp.CustomerGroup_id=kt.CustomerGroup_id
		left join
			FAF.FA_RT_AccountType actp
		on actp.UserBank_id=kt.UserBank_id and actp.AccountType_cd=kt.AccountType_cd
		where kt.AccountCloseDate is NULL or kt.AccountCloseDate>@dt),
	acnbl as
		(select distinct kt.UserBank_id,grp.Customer_id,kt.AccountBlockType_cd,acbl.Description as AccountBlockType
		from 
			FAF.FA_ACCOUNTS kt
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=kt.UserBank_id and grp.CustomerGroup_id=kt.CustomerGroup_id
		left join
			FAF.FA_RT_AccountBlockType acbl
		on acbl.UserBank_id=kt.UserBank_id and acbl.AccountBlockType_cd=kt.AccountBlockType_cd
		where kt.AccountCloseDate is NULL or kt.AccountCloseDate>@dt)
SELECT
	case 
		when cl.CustomerBuisinesCategory_cd in ('0506','0605','0905','0918')
		then 1
		else 0
	end as HighRiskIndustry_flag,
	case
		when len(isnull(cl.CustomerFirstName,''))<=1 then 1
		when len(isnull(cl.CustomerLastName,''))<=1 then 1
		when cl.CustomerDateOfBirth is NULL then 1
		when cl.CustomerCitizenship1_cd is NULL then 1
		when cl.CustomerIncorporationNumber is NULL 
			and (cl.CustomerPassportNumber is NULL or cl.CustomerPassportCountry_cd is NULL) 
			and (iddoc.IdentityDocNumber is NULL or iddoc.IdentityDocCountry_cd is NULL)
		then 1
		else 0
	end as IncompleteLegitimation_flag,
	case when accl.UserBank_id is not NULL then 1 else 0 end as SHORT_KT_flag,
	accl.SHORT_KT_AMOUNT,
	accl.SHORT_KT_DURATION,
	accl.Account_id as SHORT_KT_Account_id,
	case when nl.UserBank_id is not NULL then 1 else 0 end as NewLink_flag,
	case when ns.UserBank_id is not NULL then 1 else 0 end as NewSignatory_flag,
	isnull(mcntrp.cnt,0) as CNT_INFLOW_Counterparty,
	case when coll.UserBank_id is not NULL then 1 else 0 end as Link_to_CPCollector_flag,
	coll.CPCollector_LIST,
	NULL as KD_NegativeCharacter_flag,
	-- END of the bank-specific variables definition

	cl.UserBank_id,
	cl.Customer_id,
	cl.Customer_nr,
	cl.Branch_id,
	cl.CustomerType_cd,
	replace(rtrim(ltrim(replace(replace(replace(ctp.Description,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerType_Descr,
	cl.Profile_code,
	cl.ProfileType_cd,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerFirstName,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerFirstName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerLastName,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerLastName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerFullName,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerFullName,
	cl.CustomerOpeningDate,
	cl.CustomerStatus_cd,
	replace(rtrim(ltrim(replace(replace(replace(cst.Description,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerStatus_Descr,
	cl.CustomerDateOfBirth,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPlaceOfBirth,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerPlaceOfBirth,
	cl.CustomerCountryOfBirth_cd,
	cl.CustomerDateOfDeath,
	cl.CustomerCitizenship1_cd,
	cntr1.CountryName as CustomerCitizenship1_Name,
	cntr1.CountryISO2 as CustomerCitizenship1_ISO2,
	cl.CustomerCitizenship2_cd,
	cl.CustomerGender_cd,
	cl.CustomerTitle_cd,
	cl.CustomerOccupation_cd,
	cl.CustomerTaxCountry_cd,
	cl.CustomerDomicile_flag,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerIncorporationNumber,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerIncorporationNumber,
	cl.CustomerBuisinesCategory_cd,
	cl.CustomerLegalForm_cd,
	replace(rtrim(ltrim(replace(replace(replace(lf.Description,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerLegalForm_Descr,
	cl.CustomerParentCompanyResid_cd,
	cl.CustomerLegalCategory_cd,
	cl.CustomerFATCAStatus_cd,
	cl.CustomerBusinessRelation_cd,
	cl.CustomerIDCheckType_cd,
	cl.CustomerReferredEAM_cd,
	cl.CustomerComplexStructure_flag,
	cl.CustomerGroupType_cd,
	cl.CustomerSizeCategory_cd,
	cl.CustomerLastChangeDate,
	cl.CustomerCivilStatus_cd,
	cl.CustomerRiskCategory_cd,
	cl.CustomerSegment_cd,
	cl.CustomerVip_flag,
	cl.CustomerCategory_cd,
	replace(rtrim(ltrim(replace(replace(replace(cct.Description,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerCategory_Descr,
	cl.CustomerCapital,
	isnull(cl.Bank_flag,0) as Bank_flag,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerCommercialName,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerCommercialName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerEmail,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerEmail,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerUrl,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerUrl,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerTaxNumber,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerTaxNumber,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerTaxRegNumber,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerTaxRegNumber,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerMiddleName,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerMiddleName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPrefixName,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerPrefixName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerMotherName,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerMotherName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerAlias,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerAlias,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerSSN,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerSSN,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPassportNumber,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerPassportNumber,
	cl.CustomerPassportCountry_cd,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerIDNumber,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerIDNumber,
	cl.CustomerCitizenship3_cd,
	cl.CustomerResidence_cd,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerSourceOfWealth,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerSourceOfWealth,
	cl.CustomerSalary_flag,
	cl.CustomerEducationLevel_cd,
	cl.CustomerNetIncome,
	cl.CustomerAddIncome,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPhoneNumber,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerPhoneNumber,
	cl.SAR_flag,
	cl.CustomerPepStatus_cd,
	replace(rtrim(ltrim(replace(replace(replace(pepst.Description,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerPepStatus_Descr,
	replace(rtrim(ltrim(replace(replace(replace(cl.PEP_Description,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as PEP_Description,
	cl.PEP_Country_cd,
	cl.CustomerPEPCategory_cd,
	cl.CustomerSanctionStatus_flag,
	cl.CustomerCrimeStatus_flag,
	cl.CustomerInternStatus_flag,
	cl.CustomerPepStatusOnboard_flag,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerComment,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerComment, 

	replace(rtrim(ltrim(replace(replace(replace(adr.AddressStreet,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as AddressStreet,
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressTown,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as AddressTown,
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressCity,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as AddressCity,
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressRegion,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as AddressRegion,
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressPostCode,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as AddressPostCode,
	adr.AddressCountry_cd,
	adr.CountryName as AddressCountry_Name,
	adr.CountryISO2 as AddressCountry_ISO2,
	adr.AddressType_cd,
	adr.AddressOpenDate,
	adr.AddressExpiryDate,
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressComment,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as AddressComment,

	isnull(ac_all.TOTAL_SALDO,0) as [KD_TOTAL_SALDO],
	isnull(ac_all.KT_CNT,0) as KT_CNT,
	isnull(ac_all.KT_CNT_90d,0) as KT_CNT_90d,
	isnull(ac_all.KT_CNT_3d,0) as KT_CNT_3d,
	isnull(ac_all.max_Partner_code,0) as max_Partner_code,
	datediff(day,acop.AccountOpenDate,@dt) as [MAX_KT_DURATION],
	datediff(year,cl.CustomerDateOfBirth,@dt) as [KD_AGE],
	datediff(day,cl.CustomerOpeningDate,@dt) as [KD_DURATION],
	datediff(day,cl.CustomerLastChangeDate,@dt) as [KD_LAST_CHANGE],
	case when cl.CustomerDateOfDeath is not NULL then 1 else 0 end as [DEATH_flag],
	case when lnk.UserBank_id is not null then 1 else 0 end as [Links_01],
	case when vt.UserBank_id is not null then 1 else 0 end as [Contracts_01],
	case when pepr.UserBank_id is not null then 1 else 0 end as [PEP_RELATED_01],
	lnk.LinkType_list,
	kdkd.CustomerB_id_list,
	emp.EmploymentType_list,
	fnd.FundType_list,
	isnull(sts.SUM_INFLOW_1d,0) as SUM_INFLOW_1d,
	isnull(sts.SUM_INFLOW_3d,0) as SUM_INFLOW_3d,
	isnull(sts.SUM_INFLOW_7d,0) as SUM_INFLOW_7d,
	isnull(sts.SUM_INFLOW_10d,0) as SUM_INFLOW_10d,
	isnull(sts.SUM_INFLOW_30d,0) as SUM_INFLOW_30d,
	isnull(sts.SUM_INFLOW_3m,0) as SUM_INFLOW_3m,
	isnull(sts.SUM_INFLOW_6m,0) as SUM_INFLOW_6m,
	isnull(sts.SUM_INFLOW_12m,0) as SUM_INFLOW_12m,
	isnull(sts.SUM_INFLOW_24m,0) as SUM_INFLOW_24m,
	isnull(sts.CNT_INFLOW_1d,0) as CNT_INFLOW_1d,
	isnull(sts.CNT_INFLOW_3d,0) as CNT_INFLOW_3d,
	isnull(sts.CNT_INFLOW_7d,0) as CNT_INFLOW_7d,
	isnull(sts.CNT_INFLOW_10d,0) as CNT_INFLOW_10d,
	isnull(sts.CNT_INFLOW_30d,0) as CNT_INFLOW_30d,
	isnull(sts.CNT_INFLOW_3m,0) as CNT_INFLOW_3m,
	isnull(sts.CNT_INFLOW_6m,0) as CNT_INFLOW_6m,
	isnull(sts.CNT_INFLOW_12m,0) as CNT_INFLOW_12m,
	isnull(sts.CNT_INFLOW_24m,0) as CNT_INFLOW_24m,
	isnull(sts.SUM_OUTFLOW_1d,0) as SUM_OUTFLOW_1d,
	isnull(sts.SUM_OUTFLOW_3d,0) as SUM_OUTFLOW_3d,
	isnull(sts.SUM_OUTFLOW_7d,0) as SUM_OUTFLOW_7d,
	isnull(sts.SUM_OUTFLOW_10d,0) as SUM_OUTFLOW_10d,
	isnull(sts.SUM_OUTFLOW_30d,0) as SUM_OUTFLOW_30d,
	isnull(sts.SUM_OUTFLOW_3m,0) as SUM_OUTFLOW_3m,
	isnull(sts.SUM_OUTFLOW_6m,0) as SUM_OUTFLOW_6m,
	isnull(sts.SUM_OUTFLOW_12m,0) as SUM_OUTFLOW_12m,
	isnull(sts.SUM_OUTFLOW_24m,0) as SUM_OUTFLOW_24m,
	isnull(sts.CNT_OUTFLOW_1d,0) as CNT_OUTFLOW_1d,
	isnull(sts.CNT_OUTFLOW_3d,0) as CNT_OUTFLOW_3d,
	isnull(sts.CNT_OUTFLOW_7d,0) as CNT_OUTFLOW_7d,
	isnull(sts.CNT_OUTFLOW_10d,0) as CNT_OUTFLOW_10d,
	isnull(sts.CNT_OUTFLOW_30d,0) as CNT_OUTFLOW_30d,
	isnull(sts.CNT_OUTFLOW_3m,0) as CNT_OUTFLOW_3m,
	isnull(sts.CNT_OUTFLOW_6m,0) as CNT_OUTFLOW_6m,
	isnull(sts.CNT_OUTFLOW_12m,0) as CNT_OUTFLOW_12m,
	isnull(sts.CNT_OUTFLOW_24m,0) as CNT_OUTFLOW_24m,
	isnull(sts.SUM_INFLOW_Dubious_3m,0) as SUM_INFLOW_Dubious_3m,
	isnull(sts.CNT_INFLOW_Dubious_3m,0) as CNT_INFLOW_Dubious_3m,
	isnull(sts.SUM_INFLOW_CRYPTO_30d,0) as SUM_INFLOW_CRYPTO_30d,
	isnull(sts.CNT_INFLOW_CRYPTO_30d,0) as CNT_INFLOW_CRYPTO_30d,
	isnull(sts.SUM_OUTFLOW_CRYPTO_30d,0) as SUM_OUTFLOW_CRYPTO_30d,
	isnull(sts.CNT_OUTFLOW_CRYPTO_30d,0) as CNT_OUTFLOW_CRYPTO_30d,
	isnull(sts.SUM_OUTFLOW_CASH_30d,0) as SUM_OUTFLOW_CASH_30d,
	isnull(sts.CNT_OUTFLOW_CASH_30d,0) as CNT_OUTFLOW_CASH_30d,
	isnull(sts.SUM_OUTFLOW_CASH_7d,0) as SUM_OUTFLOW_CASH_7d,
	isnull(sts.CNT_OUTFLOW_CASH_7d,0) as CNT_OUTFLOW_CASH_7d,
	isnull(sts.SUM_OUTFLOW_CASH_3d,0) as SUM_OUTFLOW_CASH_3d,
	isnull(sts.CNT_OUTFLOW_CASH_3d,0) as CNT_OUTFLOW_CASH_3d,
	isnull(sts.SUM_FOREIGN_INFLOW_3m,0) as SUM_FOREIGN_INFLOW_3m,
	isnull(sts.SUM_FOREIGN_INFLOW_12m,0) as SUM_FOREIGN_INFLOW_12m,
	isnull(sts.SUM_FOREIGN_OUTFLOW_3m,0) as SUM_FOREIGN_OUTFLOW_3m,
	isnull(sts.SUM_FOREIGN_OUTFLOW_12m,0) as SUM_FOREIGN_OUTFLOW_12m,
	isnull(sts.CNT_SMALL_INFLOWS_30d,0) as CNT_SMALL_INFLOWS_30d,
	isnull(sts.CNT_SMALL_OUTFLOWS_30d,0) as CNT_SMALL_OUTFLOWS_30d,
	isnull(sts.SUM_RETURNDEBIT_30d,0) as SUM_RETURNDEBIT_30d,
	isnull(sts.CNT_RETURNDEBIT_30d,0) as CNT_RETURNDEBIT_30d,
	isnull(sts.SUM_ChequeDeposit_3d,0) as SUM_ChequeDeposit_3d,
	isnull(sts.SUM_INFLOW_3RD_PARTY_14d,0) as SUM_INFLOW_3RD_PARTY_14d,
	isnull(sts.CNT_INFLOW_3RD_PARTY_14d,0) as CNT_INFLOW_3RD_PARTY_14d,
	isnull(sts.SUM_INFLOW_MISMATCH_RECIPIENT_3d,0) as SUM_INFLOW_MISMATCH_RECIPIENT_3d,
	isnull(sts.CNT_INFLOW_MISMATCH_RECIPIENT_3d,0) as CNT_INFLOW_MISMATCH_RECIPIENT_3d,
	isnull(sts.SUM_INFLOW_LOAN_30d,0) as SUM_INFLOW_LOAN_30d,
	isnull(sts.CNT_INFLOW_LOAN_30d,0) as CNT_INFLOW_LOAN_30d,
	isnull(sts.SUM_GAMBLING_7d,0) as SUM_GAMBLING_7d,
	isnull(sts.SUM_GAMBLING_30d,0) as SUM_GAMBLING_30d,
	isnull(sts.SUM_GAMBLING_3m,0) as SUM_GAMBLING_3m,
	isnull(sts.CNT_GAMBLING_7d,0) as CNT_GAMBLING_7d,
	isnull(sts.CNT_GAMBLING_30d,0) as CNT_GAMBLING_30d,
	isnull(sts.CNT_GAMBLING_3m,0) as CNT_GAMBLING_3m,
	isnull(sts.SUM_INFLOW_1d,0)+isnull(sts.SUM_OUTFLOW_1d,0) as SUM_INOUT_1d,
	isnull(sts.SUM_INFLOW_3d,0)+isnull(sts.SUM_OUTFLOW_3d,0) as SUM_INOUT_3d,
	isnull(sts.SUM_INFLOW_7d,0)+isnull(sts.SUM_OUTFLOW_7d,0) as SUM_INOUT_7d,
	isnull(sts.SUM_INFLOW_10d,0)+isnull(sts.SUM_OUTFLOW_10d,0) as SUM_INOUT_10d,
	isnull(sts.SUM_INFLOW_30d,0)+isnull(sts.SUM_OUTFLOW_30d,0) as SUM_INOUT_30d,
	isnull(sts.SUM_INFLOW_3m,0)+isnull(sts.SUM_OUTFLOW_3m,0) as SUM_INOUT_3m,
	isnull(sts.SUM_INFLOW_6m,0)+isnull(sts.SUM_OUTFLOW_6m,0) as SUM_INOUT_6m,
	isnull(sts.SUM_INFLOW_12m,0)+isnull(sts.SUM_OUTFLOW_12m,0) as SUM_INOUT_12m,
	isnull(sts.SUM_INFLOW_24m,0)+isnull(sts.SUM_OUTFLOW_24m,0) as SUM_INOUT_24m,
	isnull(sts.CNT_INFLOW_1d,0)+isnull(sts.CNT_OUTFLOW_1d,0) as CNT_INOUT_1d,
	isnull(sts.CNT_INFLOW_3d,0)+isnull(sts.CNT_OUTFLOW_3d,0) as CNT_INOUT_3d,
	isnull(sts.CNT_INFLOW_7d,0)+isnull(sts.CNT_OUTFLOW_7d,0) as CNT_INOUT_7d,
	isnull(sts.CNT_INFLOW_10d,0)+isnull(sts.CNT_OUTFLOW_10d,0) as CNT_INOUT_10d,
	isnull(sts.CNT_INFLOW_30d,0)+isnull(sts.CNT_OUTFLOW_30d,0) as CNT_INOUT_30d,
	isnull(sts.CNT_INFLOW_3m,0)+isnull(sts.CNT_OUTFLOW_3m,0) as CNT_INOUT_3m,
	isnull(sts.CNT_INFLOW_6m,0)+isnull(sts.CNT_OUTFLOW_6m,0) as CNT_INOUT_6m,
	isnull(sts.CNT_INFLOW_12m,0)+isnull(sts.CNT_OUTFLOW_12m,0) as CNT_INOUT_12m,
	isnull(sts.CNT_INFLOW_24m,0)+isnull(sts.CNT_OUTFLOW_24m,0) as CNT_INOUT_24m,
	isnull(sts.SUM_FOREIGN_INFLOW_3m,0)+isnull(sts.SUM_FOREIGN_OUTFLOW_3m,0) as SUM_FOREIGN_INOUT_3m,
	isnull(sts.SUM_FOREIGN_INFLOW_12m,0)+isnull(sts.SUM_FOREIGN_OUTFLOW_12m,0) as SUM_FOREIGN_INOUT_12m,
	isnull(sts.SUM_INFLOW_1d,0)/isnull(case when sts.CNT_INFLOW_1d=0 then 1 else sts.CNT_INFLOW_1d end,1) as AVG_INFLOW_1d,
	isnull(sts.SUM_INFLOW_3d,0)/isnull(case when sts.CNT_INFLOW_3d=0 then 1 else sts.CNT_INFLOW_3d end,1) as AVG_INFLOW_3d,
	isnull(sts.SUM_INFLOW_7d,0)/isnull(case when sts.CNT_INFLOW_7d=0 then 1 else sts.CNT_INFLOW_7d end,1) as AVG_INFLOW_7d,
	isnull(sts.SUM_INFLOW_10d,0)/isnull(case when sts.CNT_INFLOW_10d=0 then 1 else sts.CNT_INFLOW_10d end,1) as AVG_INFLOW_10d,
	isnull(sts.SUM_INFLOW_30d,0)/isnull(case when sts.CNT_INFLOW_30d=0 then 1 else sts.CNT_INFLOW_30d end,1) as AVG_INFLOW_30d,
	isnull(sts.SUM_INFLOW_3m,0)/isnull(case when sts.CNT_INFLOW_3m=0 then 1 else sts.CNT_INFLOW_3m end,1) as AVG_INFLOW_3m,
	isnull(sts.SUM_INFLOW_6m,0)/isnull(case when sts.CNT_INFLOW_6m=0 then 1 else sts.CNT_INFLOW_6m end,1) as AVG_INFLOW_6m,
	isnull(sts.SUM_INFLOW_12m,0)/isnull(case when sts.CNT_INFLOW_12m=0 then 1 else sts.CNT_INFLOW_12m end,1) as AVG_INFLOW_12m,
	isnull(sts.SUM_INFLOW_24m,0)/isnull(case when sts.CNT_INFLOW_24m=0 then 1 else sts.CNT_INFLOW_24m end,1) as AVG_INFLOW_24m,
	isnull(sts.SUM_OUTFLOW_1d,0)/isnull(case when sts.CNT_OUTFLOW_1d=0 then 1 else sts.CNT_OUTFLOW_1d end,1) as AVG_OUTFLOW_1d,
	isnull(sts.SUM_OUTFLOW_3d,0)/isnull(case when sts.CNT_OUTFLOW_3d=0 then 1 else sts.CNT_OUTFLOW_3d end,1) as AVG_OUTFLOW_3d,
	isnull(sts.SUM_OUTFLOW_7d,0)/isnull(case when sts.CNT_OUTFLOW_7d=0 then 1 else sts.CNT_OUTFLOW_7d end,1) as AVG_OUTFLOW_7d,
	isnull(sts.SUM_OUTFLOW_10d,0)/isnull(case when sts.CNT_OUTFLOW_10d=0 then 1 else sts.CNT_OUTFLOW_10d end,1) as AVG_OUTFLOW_10d,
	isnull(sts.SUM_OUTFLOW_30d,0)/isnull(case when sts.CNT_OUTFLOW_30d=0 then 1 else sts.CNT_OUTFLOW_30d end,1) as AVG_OUTFLOW_30d,
	isnull(sts.SUM_OUTFLOW_3m,0)/isnull(case when sts.CNT_OUTFLOW_3m=0 then 1 else sts.CNT_OUTFLOW_3m end,1) as AVG_OUTFLOW_3m,
	isnull(sts.SUM_OUTFLOW_6m,0)/isnull(case when sts.CNT_OUTFLOW_6m=0 then 1 else sts.CNT_OUTFLOW_6m end,1) as AVG_OUTFLOW_6m,
	isnull(sts.SUM_OUTFLOW_12m,0)/isnull(case when sts.CNT_OUTFLOW_12m=0 then 1 else sts.CNT_OUTFLOW_12m end,1) as AVG_OUTFLOW_12m,
	isnull(sts.SUM_OUTFLOW_24m,0)/isnull(case when sts.CNT_OUTFLOW_24m=0 then 1 else sts.CNT_OUTFLOW_24m end,1) as AVG_OUTFLOW_24m,
	isnull(sld30d.MAX_KD_TOTAL_SALDO_30d,0) as MAX_KD_TOTAL_SALDO_30d,
	isnull(sld3d.MAX_KD_TOTAL_SALDO_3d,0) as MAX_KD_TOTAL_SALDO_3d,
	isnull(ac_all.SUM_CURRENT_SALDO,0) as SUM_KD_CURRENT_SALDO,
	isnull(ac_all.SUM_SAVINGS_SALDO,0) as SUM_KD_SAVINGS_SALDO,
	isnull(ac_all.SUM_LOAN_SALDO,0) as SUM_KD_LOAN_SALDO,
	isnull(ac_all.SUM_CREDITCARD_SALDO,0) as SUM_KD_CREDITCARD_SALDO,
	isnull(ac_all.SUM_BUILDINGSAVINGS_SALDO,0) as SUM_KD_BUILDINGSAVINGS_SALDO,
	isnull(ac_all.SUM_CAPITAL_SALDO,0) as SUM_KD_CAPITAL_SALDO,
	isnull(ac_all.SUM_LIFEINSURANCE_SALDO,0) as SUM_KD_LIFEINSURANCE_SALDO,
	isnull(ac_all.SUM_NONLIFEINSURANCE_SALDO,0) as SUM_KD_NONLIFEINSURANCE_SALDO,
	isnull(ac_all.CNT_CURRENT_SALDO,0) as CNT_KD_CURRENT_SALDO,
	isnull(ac_all.CNT_SAVINGS_SALDO,0) as CNT_KD_SAVINGS_SALDO,
	isnull(ac_all.CNT_LOAN_SALDO,0) as CNT_KD_LOAN_SALDO,
	isnull(ac_all.CNT_CREDITCARD_SALDO,0) as CNT_KD_CREDITCARD_SALDO,
	isnull(ac_all.CNT_BUILDINGSAVINGS_SALDO,0) as CNT_KD_BUILDINGSAVINGS_SALDO,
	isnull(ac_all.CNT_CAPITAL_SALDO,0) as CNT_KD_CAPITAL_SALDO,
	isnull(ac_all.CNT_LIFEINSURANCE_SALDO,0) as CNT_KD_LIFEINSURANCE_SALDO,
	isnull(ac_all.CNT_NONLIFEINSURANCE_SALDO,0) as CNT_KD_NONLIFEINSURANCE_SALDO,
	isnull(ac_all.SUM_CURRENT_SALDO,0)/isnull(case when ac_all.SUM_CURRENT_SALDO=0 then 1 else ac_all.CNT_CURRENT_SALDO end,1) as AVG_KD_CURRENT_SALDO,
	isnull(ac_all.SUM_SAVINGS_SALDO,0)/isnull(case when ac_all.SUM_SAVINGS_SALDO=0 then 1 else ac_all.CNT_SAVINGS_SALDO end,1) as AVG_KD_SAVINGS_SALDO,
	isnull(ac_all.SUM_LOAN_SALDO,0)/isnull(case when ac_all.SUM_LOAN_SALDO=0 then 1 else ac_all.CNT_LOAN_SALDO end,1) as AVG_KD_LOAN_SALDO,
	isnull(prd.CNT_KD_Products,0) as CNT_KD_Products,
	ac_all.LastAccountOpenDate,
	asof.AccountSourceOfFunds_list,
	prd_cd.ProductCode_cd_list,
	prd_.ProductCode_list,
	actp_cd.AccountType_cd_list,
	actp_.AccountType_list,
	acbl_cd.AccountBlockType_cd_list,
	acbl_.AccountBlockType_list,
	case when isnull(cl.CustomerCategory_cd,'')<>'V' and agnt.UserBank_id is not NULL then 1 else 0 end as AgentContact_flag,
	--BHV
	NULL as [KD_BHVCLUSTER],
	NULL as [KD_BHV_THRESHOLD], 
	NULL as [KD_BHV_RISK],
	NULL as [MAX_DAY_SUM_AMOUNT_1Y],
	NULL as [MAX_CNT_MONTH_TRANS_1Y],
	NULL as [CNT_MONTH_WITH_TRANS_1Y],
	NULL as [MAX_TRANSACTION_1Y],
	NULL as [MAX_MONTH_SUM_AMOUNT_1Y],
	NULL as [MAX_MONTH_SUM_INFLOW_1Y],
	NULL as [MAX_MONTH_SUM_OUTFLOW_1Y],
	NULL as [AVG_KD_SALDO_1Y],
	NULL as [MAX_KD_SALDO_1Y],
	/*
	isnull(clst1.prev_BHVCluster,0) as [KD_BHVCLUSTER],
	case 
		when thr.thr_q75_meantop_W_AmountCHF>(case when cl.kd_htyp_cd<3 then @min_PAmountCHF else @min_EAmountCHF end)
		then thr.thr_q75_meantop_W_AmountCHF 
		else 
			case 
				when cl.CustomerType_cd = '1' then @min_PAmountCHF
				when cl.CustomerType_cd = '2' then @min_PAmountCHF
				else @min_EAmountCHF 
			end
	end as [KD_BHV_THRESHOLD], 
	isnull(clst1.Risk,0) as [KD_BHV_RISK],
	isnull(clst1.prev_max_day_sum_Amount,0) as [MAX_DAY_SUM_AMOUNT_1Y],
	isnull(clst1.prev_max_N_month_trans,0) as [MAX_CNT_MONTH_TRANS_1Y],	
	isnull(clst1.cnt_month,0) as [CNT_MONTH_WITH_TRANS_1Y],
	isnull(clst1.max_Amount,0) as [MAX_TRANSACTION_1Y],
	isnull(clst1.prev_max_sum_Amount,0) as [MAX_MONTH_SUM_AMOUNT_1Y],
	isnull(clst1.prev_max_sum_Inflow,0) as [MAX_MONTH_SUM_INFLOW_1Y],
	isnull(clst1.prev_max_sum_Outflow,0) as [MAX_MONTH_SUM_OUTFLOW_1Y],
	isnull(sld1.avg_Saldo,0) as [AVG_KD_SALDO_1Y],
	isnull(sld1.max_Saldo,0) as [MAX_KD_SALDO_1Y],
	*/
	cast(convert(varchar,@dt,112) as int) as T_DATE
from
	FAF.FA_CUSTOMERS cl
left join
	(select UserBank_id,Customer_id,max(Partner_code) as max_Partner_code,
		count(*) as KT_CNT,sum(SaldoLocal) as TOTAL_SALDO,max(SaldoDate) as max_SaldoDate,
		sum(case when datediff(day,AccountOpenDate,@dt)<=89 then 1 else 0 end) as KT_CNT_90d,
		sum(case when datediff(day,AccountOpenDate,@dt)<=2 then 1 else 0 end) as KT_CNT_3d,
		sum(case when [Group]='CURRENT' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_CURRENT_SALDO,
		sum(case when [Group]='SAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_SAVINGS_SALDO,
		sum(case when [Group]='LOAN' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_LOAN_SALDO,
		sum(case when [Group]='CREDITCARD' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_CREDITCARD_SALDO,
		sum(case when [Group]='BUILDINGSAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_BUILDINGSAVINGS_SALDO,
		sum(case when [Group]='CAPITAL' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_CAPITAL_SALDO,
		sum(case when [Group]='LIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_LIFEINSURANCE_SALDO,
		sum(case when [Group]='NONLIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_NONLIFEINSURANCE_SALDO,
		sum(case when [Group]='CURRENT' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_CURRENT_SALDO,
		sum(case when [Group]='SAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_SAVINGS_SALDO,
		sum(case when [Group]='LOAN' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_LOAN_SALDO,
		sum(case when [Group]='CREDITCARD' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_CREDITCARD_SALDO,
		sum(case when [Group]='BUILDINGSAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_BUILDINGSAVINGS_SALDO,
		sum(case when [Group]='CAPITAL' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_CAPITAL_SALDO,
		sum(case when [Group]='LIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_LIFEINSURANCE_SALDO,
		sum(case when [Group]='NONLIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_NONLIFEINSURANCE_SALDO,
		max(AccountOpenDate) as LastAccountOpenDate
	from
		(select *, 
			row_number() over (partition by UserBank_id,Customer_id,Account_id order by SaldoDate desc) as rn 
		from sld_all where SaldoDate<=@dt) ss
	where rn=1
	group by UserBank_id,Customer_id) ac_all
on cl.UserBank_id=ac_all.UserBank_id and cl.Customer_id=ac_all.Customer_id
left join
	(select UserBank_id,Customer_id,IdentityDocNumber,IdentityDocCountry_cd,
		row_number() over (partition by UserBank_id,Customer_id order by Profile_code,IdentityDoc_id desc) as rn
	from FAF.FA_IDENTITY_DOCUMENTS
	where UserBank_id=@F_BANK_ID 
		and isnull(IdentityDocIssueDate,convert(date,'19010101',112))<=@dt 
		and isnull(IdentityDocExpiryDate,convert(date,'20991231',112))>=@dt) iddoc
on iddoc.UserBank_id=cl.UserBank_id and iddoc.Customer_id=cl.Customer_id and iddoc.rn=1
left join
	(select ad.*,cntr.CountryISO2,cntr.CountryName,
		row_number() over (partition by ad.UserBank_id,ad.Customer_id order by isnull(cntr.RiskCategory,7) desc,ad.Address_id desc) as rn
	from
		FAF.FA_ADDRESSES ad
	left join
		FAF.FA_RT_COUNTRY cntr
	on cntr.UserBank_id=ad.UserBank_id and cntr.Country_cd=ad.AddressCountry_cd
	where ad.UserBank_id=@F_BANK_ID 
		and isnull(ad.AddressOpenDate,convert(date,'19010101',112))<=@dt 
		and isnull(ad.AddressExpiryDate,convert(date,'20991231',112))>=@dt
		and ad.Profile_code=0) adr
on adr.UserBank_id=cl.UserBank_id and adr.Customer_id=cl.Customer_id and adr.rn=1
left join
	nl
on nl.UserBank_id=cl.UserBank_id and nl.CustomerA_id=cl.Customer_id
left join
	ns
on ns.UserBank_id=cl.UserBank_id and ns.Customer_id=cl.Customer_id
left join
	pepr
on pepr.UserBank_id=cl.UserBank_id and pepr.CustomerA_id=cl.Customer_id
left join
	(select t1.UserBank_id,t1.CustomerA_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(t1.LinkType_cd as varchar(4000)) + '}'),',') within group (order by t1.LinkType_cd),1,4000) as varchar(4000)) as LinkType_list
	from 
		(select distinct UserBank_id,CustomerA_id,LinkType_cd from links0 where LinkType_cd is not NULL) t1
	group by t1.UserBank_id,t1.CustomerA_id) lnk
on cl.UserBank_id=lnk.UserBank_id and cl.Customer_id=lnk.CustomerA_id
left join
	(select t2.UserBank_id,t2.CustomerA_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(t2.CustomerB_id as varchar(4000)) + '}'),',') within group (order by t2.CustomerB_id),1,4000) as varchar(4000)) as CustomerB_id_list
	from 
		(select distinct links0.UserBank_id,links0.CustomerA_id,grp.Customer_id as CustomerB_id
		from 
			links0
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=links0.UserBank_id and grp.CustomerGroup_id=links0.CustomerGroupB_id
		where links0.CustomerA_id<>grp.Customer_id) t2
	group by t2.UserBank_id,t2.CustomerA_id) kdkd
on cl.UserBank_id=kdkd.UserBank_id and cl.Customer_id=kdkd.CustomerA_id
left join
	(select distinct UserBank_id,CustomerA_id from links0
	--where LinkMode_cd in ('vt1','vt2')
	) vt
on cl.UserBank_id=vt.UserBank_id and cl.Customer_id=vt.CustomerA_id
left join
	(select s0.UserBank_id,s0.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(s0.EmploymentType_cd as varchar(4000)) + '}'),',') within group (order by s0.EmploymentType_cd),1,4000) as varchar(4000)) as EmploymentType_list
	from 
		(select distinct UserBank_id,Customer_id,EmploymentType_cd 
		from FAF.FA_EMPLOYMENTS 
		where UserBank_id=@F_BANK_ID and EmploymentType_cd is not null
			and isnull(EmploymentOpenDate,convert(date,'19010101',112))<=@dt 
			and isnull(EmploymentExpiryDate,convert(date,'20991231',112))>=@dt
			and Profile_code=0) s0
	group by s0.UserBank_id,s0.Customer_id) emp
on emp.UserBank_id=cl.UserBank_id and emp.Customer_id=cl.Customer_id
left join
	(select q0.UserBank_id,q0.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(q0.FundType_cd as varchar(4000)) + '}'),',') within group (order by q0.FundType_cd),1,4000) as varchar(4000)) as FundType_list
	from 
		(select distinct UserBank_id,Customer_id,FundType_cd 
		from FAF.FA_FUNDS 
		where UserBank_id=@F_BANK_ID and FundType_cd is not null
			and isnull(FundOpenDate,convert(date,'19010101',112))<=@dt 
			and isnull(FundExpiryDate,convert(date,'20991231',112))>=@dt) q0
	group by q0.UserBank_id,q0.Customer_id) fnd
on fnd.UserBank_id=cl.UserBank_id and fnd.Customer_id=cl.Customer_id
left join
	(select g0.UserBank_id,g0.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(g0.AccountSourceOfFunds_cd as varchar(4000)) + '}'),',') within group (order by g0.AccountSourceOfFunds_cd),1,4000) as varchar(4000)) as AccountSourceOfFunds_list
	from 
		acsc g0
	group by g0.UserBank_id,g0.Customer_id) asof
on asof.UserBank_id=cl.UserBank_id and asof.Customer_id=cl.Customer_id
left join
	FAF.FA_RT_COUNTRY cntr1
on cntr1.UserBank_id=cl.UserBank_id and cntr1.Country_cd=cl.CustomerCitizenship1_cd
left join
	FAF.FA_RT_CustomerLegalForm lf
on lf.UserBank_id=cl.UserBank_id and lf.CustomerLegalForm_cd=cl.CustomerLegalForm_cd
left join
	FAF.FA_RT_CustomerCategory cct
on cct.UserBank_id=cl.UserBank_id and cct.CustomerCategory_cd=cl.CustomerCategory_cd
left join
	FAF.FA_RT_CustomerType ctp
on ctp.UserBank_id=cl.UserBank_id and ctp.CustomerType_cd=cl.CustomerType_cd
left join
	FAF.FA_RT_CustomerStatus cst
on cst.UserBank_id=cl.UserBank_id and cst.CustomerStatus_cd=cl.CustomerStatus_cd
left join
	FAF.FA_RT_CustomerPepStatus pepst
on pepst.UserBank_id=cl.UserBank_id and pepst.CustomerPepStatus_cd=cl.CustomerPepStatus_cd
left join
	accl
on accl.UserBank_id=cl.UserBank_id and accl.Customer_id=cl.Customer_id and accl.rn=1
left join
	mcntrp
on mcntrp.UserBank_id=cl.UserBank_id and mcntrp.Customer_id=cl.Customer_id
left join
	coll
on coll.UserBank_id=cl.UserBank_id and coll.Customer_id=cl.Customer_id
left join
	(select trns.UserBank_id,trns.Customer_id,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-1,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_1d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_3d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_7d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-10,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_10d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_30d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_3m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-6,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_6m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-12,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_12m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-24,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_24m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-1,@dt) then 1 else 0 end) as CNT_INFLOW_1d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) then 1 else 0 end) as CNT_INFLOW_3d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) then 1 else 0 end) as CNT_INFLOW_7d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-10,@dt) then 1 else 0 end) as CNT_INFLOW_10d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) then 1 else 0 end) as CNT_INFLOW_30d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) then 1 else 0 end) as CNT_INFLOW_3m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-6,@dt) then 1 else 0 end) as CNT_INFLOW_6m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-12,@dt) then 1 else 0 end) as CNT_INFLOW_12m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-24,@dt) then 1 else 0 end) as CNT_INFLOW_24m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-1,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_1d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_3d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_7d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-10,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_10d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_3m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-6,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_6m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-12,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_12m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-24,@dt) then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_24m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-1,@dt) then 1 else 0 end) as CNT_OUTFLOW_1d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) then 1 else 0 end) as CNT_OUTFLOW_3d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) then 1 else 0 end) as CNT_OUTFLOW_7d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-10,@dt) then 1 else 0 end) as CNT_OUTFLOW_10d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) then 1 else 0 end) as CNT_OUTFLOW_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) then 1 else 0 end) as CNT_OUTFLOW_3m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-6,@dt) then 1 else 0 end) as CNT_OUTFLOW_6m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-12,@dt) then 1 else 0 end) as CNT_OUTFLOW_12m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-24,@dt) then 1 else 0 end) as CNT_OUTFLOW_24m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) and trns.DubiousReason_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_Dubious_3m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) and trns.DubiousReason_flag=1 then 1 else 0 end) as CNT_INFLOW_Dubious_3m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.CryptoRelated_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_CRYPTO_30d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.CryptoRelated_flag=1 then 1 else 0 end) as CNT_INFLOW_CRYPTO_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.CryptoRelated_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_CRYPTO_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.CryptoRelated_flag=1 then 1 else 0 end) as CNT_OUTFLOW_CRYPTO_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.Cash_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_CASH_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.Cash_flag=1 then 1 else 0 end) as CNT_OUTFLOW_CASH_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) and trns.Cash_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_CASH_7d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) and trns.Cash_flag=1 then 1 else 0 end) as CNT_OUTFLOW_CASH_7d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) and trns.Cash_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_OUTFLOW_CASH_3d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) and trns.Cash_flag=1 then 1 else 0 end) as CNT_OUTFLOW_CASH_3d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) and trns.FOREIGN_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_FOREIGN_INFLOW_3m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-12,@dt) and trns.FOREIGN_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_FOREIGN_INFLOW_12m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) and trns.FOREIGN_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_FOREIGN_OUTFLOW_3m,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-12,@dt) and trns.FOREIGN_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_FOREIGN_OUTFLOW_12m,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.SMALL_flag=1 then 1 else 0 end) as CNT_SMALL_INFLOWS_30d,
		sum(case when trns.InflowOutflow='O' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.SMALL_flag=1 then 1 else 0 end) as CNT_SMALL_OUTFLOWS_30d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.ReturnDebit_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_RETURNDEBIT_30d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.ReturnDebit_flag=1 then 1 else 0 end) as CNT_RETURNDEBIT_30d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) and trns.ChequeDeposit_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_ChequeDeposit_3d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-14,@dt) and trns.DifCounterparties_flag=1 and trns.Non_Current_Account_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_3RD_PARTY_14d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-14,@dt) and trns.DifCounterparties_flag=1 and trns.Non_Current_Account_flag=1 then 1 else 0 end) as CNT_INFLOW_3RD_PARTY_14d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) and trns.MismatchRecipient_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_MISMATCH_RECIPIENT_3d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-3,@dt) and trns.MismatchRecipient_flag=1 then 1 else 0 end) as CNT_INFLOW_MISMATCH_RECIPIENT_3d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.Loan_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_INFLOW_LOAN_30d,
		sum(case when trns.InflowOutflow='I' and trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.Loan_flag=1 then 1 else 0 end) as CNT_INFLOW_LOAN_30d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) and trns.Gambling_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_GAMBLING_7d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.Gambling_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_GAMBLING_30d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) and trns.Gambling_flag=1 then trns.AmountLocalCurrency else 0 end) as SUM_GAMBLING_3m,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-7,@dt) and trns.Gambling_flag=1 then 1 else 0 end) as CNT_GAMBLING_7d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(day,-30,@dt) and trns.Gambling_flag=1 then 1 else 0 end) as CNT_GAMBLING_30d,
		sum(case when trns.TransactionDate<=@dt and trns.TransactionDate>dateadd(month,-3,@dt) and trns.Gambling_flag=1 then 1 else 0 end) as CNT_GAMBLING_3m
	from 
		(select kd.UserBank_id,kd.Customer_id,tr.InflowOutflow,tr.TransactionDate,tr.AmountLocalCurrency,
			case when (select top 1 charindex(dr.[Name],tr.TransactionDescription) as chi from FAF.DubiousReason_Wuestenrot dr order by charindex(dr.[Name],tr.TransactionDescription) desc)>0 then 1 else 0 end as DubiousReason_flag,
			case when (select top 1 charindex(cr.[Name],tr.TransactionDescription) as chi from FAF.CryptoRelated_Wuestenrot cr order by charindex(cr.[Name],tr.TransactionDescription) desc)>0 then 1 else 0 end as CryptoRelated_flag,
			case when tr.TransactionType_cd in (select TransactionType_cd from FAF.CashTransactions_Wuestenrot) then 1 else 0 end as Cash_flag,
			case when tr.TransactionCountry_cd<>'AT' then 1 else 0 end as FOREIGN_flag,
			case when tr.AmountLocalCurrency<=50 then 1 else 0 end as SMALL_flag,
			case when tr.TransactionType_cd = 'IZVRUECK' then 1 else 0 end as ReturnDebit_flag,
			case when tr.TransactionType_cd in ('ECSCHECK','SCHCKEIN','SCHCKRZB','SCHECK') then 1 else 0 end as ChequeDeposit_flag,
			case when charindex(kd.CustomerFirstName,tr.CounterpartyName)=0 or charindex(kd.CustomerLastName,tr.CounterpartyName)=0 then 1 else 0 end as DifCounterparties_flag,
			case when charindex(kd.CustomerFirstName,tr.IntendendRecipient)=0 or charindex(kd.CustomerLastName,tr.IntendendRecipient)=0 then 1 else 0 end as MismatchRecipient_flag,
			case when actp.[Group] in ('SAVINGS','LOAN','CREDITCARD','BUILDINGSAVINGS','CAPITAL','LIFEINSURANCE','NONLIFEINSURANCE') then 1 else 0 end as Non_Current_Account_flag,
			case when actp.[Group] = 'LOAN' then 1 else 0 end as Loan_flag,
			case when (select top 1 charindex(gm.[Name],tr.TransactionDescription) as chi from FAF.Gambling_Wuestenrot gm order by charindex(gm.[Name],tr.TransactionDescription) desc)>0 then 1 else 0 end as Gambling_flag
		from 
			FAF.FA_TRANSACTIONS tr
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on tr.UserBank_id=grp.UserBank_id and tr.CustomerGroup_id=grp.CustomerGroup_id
		join
			FAF.FA_CUSTOMERS kd
		on kd.UserBank_id=grp.UserBank_id and kd.Customer_id=grp.Customer_id
		join
			FAF.FA_ACCOUNTS kt
		on tr.UserBank_id=kt.UserBank_id and tr.Account_id=kt.Account_id
		left join
			FAF.AccountTypes_Wuestenrot actp
		on kt.AccountType_cd=actp.AccountType_cd) trns	
	group by trns.UserBank_id,trns.Customer_id) sts
on sts.UserBank_id=cl.UserBank_id and sts.Customer_id=cl.Customer_id
left join
	(select UserBank_id,Customer_id,max(TotalSaldo) as MAX_KD_TOTAL_SALDO_30d
	from 
		(select UserBank_id,Customer_id,SaldoDate,sum(SaldoLocal) as TotalSaldo from
			(select y1.UserBank_id,y1.Customer_id,y1.SaldoDate,y2.SaldoLocal,
				row_number() over (partition by y1.UserBank_id,y1.Customer_id,y1.SaldoDate,y2.Account_id order by y2.SaldoDate desc) as rn
			from 
				(select UserBank_id,Customer_id,SaldoDate from sld_all where SaldoDate>dateadd(day,-29,@dt) and SaldoDate<=@dt
				union
				select UserBank_id,Customer_id,dateadd(day,-29,@dt) as SaldoDate from FAF.FA_CUSTOMERS) y1 
			join 
				(select UserBank_id,Customer_id,Account_id,SaldoDate,SaldoLocal from
					(select *, 
						row_number() over (partition by UserBank_id,Customer_id,Account_id order by SaldoDate desc) as rn
					from sld_all where SaldoDate<=dateadd(day,-29,@dt)) sl1
				where rn=1
				union all
				select UserBank_id,Customer_id,Account_id,SaldoDate,SaldoLocal from sld_all where SaldoDate>dateadd(day,-29,@dt) and SaldoDate<=@dt) y2 
			on y1.UserBank_id=y2.UserBank_id and y1.Customer_id=y2.Customer_id and y1.SaldoDate>=y2.SaldoDate) y3
		where rn=1
		group by UserBank_id,Customer_id,SaldoDate) y4
	group by UserBank_id,Customer_id) sld30d
on sld30d.UserBank_id=cl.UserBank_id and sld30d.Customer_id=cl.Customer_id
left join
	(select UserBank_id,Customer_id,max(TotalSaldo) as MAX_KD_TOTAL_SALDO_3d
	from 
		(select UserBank_id,Customer_id,SaldoDate,sum(SaldoLocal) as TotalSaldo from
			(select x1.UserBank_id,x1.Customer_id,x1.SaldoDate,x2.SaldoLocal,
				row_number() over (partition by x1.UserBank_id,x1.Customer_id,x1.SaldoDate,x2.Account_id order by x2.SaldoDate desc) as rn
			from 
				(select UserBank_id,Customer_id,SaldoDate from sld_all where SaldoDate>dateadd(day,-2,@dt) and SaldoDate<=@dt
				union
				select UserBank_id,Customer_id,dateadd(day,-2,@dt) as SaldoDate from FAF.FA_CUSTOMERS) x1 
			join 
				(select UserBank_id,Customer_id,Account_id,SaldoDate,SaldoLocal from
					(select *, 
						row_number() over (partition by UserBank_id,Customer_id,Account_id order by SaldoDate desc) as rn
					from sld_all where SaldoDate<=dateadd(day,-2,@dt)) sl2
				where rn=1
				union all
				select UserBank_id,Customer_id,Account_id,SaldoDate,SaldoLocal from sld_all where SaldoDate>dateadd(day,-2,@dt) and SaldoDate<=@dt) x2 
			on x1.UserBank_id=x2.UserBank_id and x1.Customer_id=x2.Customer_id and x1.SaldoDate>=x2.SaldoDate) x3
		where rn=1
		group by UserBank_id,Customer_id,SaldoDate) x4
	group by UserBank_id,Customer_id) sld3d
on sld3d.UserBank_id=cl.UserBank_id and sld3d.Customer_id=cl.Customer_id
left join
	(select UserBank_id,Customer_id,count(*) as CNT_KD_Products from acntp group by UserBank_id,Customer_id) prd
on prd.UserBank_id=cl.UserBank_id and prd.Customer_id=cl.Customer_id
left join
	(select UserBank_id,Customer_id,min(AccountOpenDate) as AccountOpenDate from sld_all group by UserBank_id,Customer_id) acop
on acop.UserBank_id=cl.UserBank_id and acop.Customer_id=cl.Customer_id
--for filtration
left join
	(select UserBank_id,Customer_id,sum(SaldoLocal) as TOTAL_SALDO
	from
		(select *, 
			row_number() over (partition by UserBank_id,Customer_id,Account_id order by SaldoDate desc) as rn 
		from sld_all where SaldoDate<=dateadd(day,-case when @TW>0 then @TW else 1 end,@dt)) f1
	where rn=1
	group by UserBank_id,Customer_id) sld_m1
on sld_m1.UserBank_id=cl.UserBank_id and sld_m1.Customer_id=cl.Customer_id
left join
	(select userbk_nr as UserBank_id,txm_f_customer_id as Customer_id,max(txm_t_date) as max_T_DATE 
	from [staging_namecheck].[dbo].[ProfilingAlerts_Filtered] where userbk_nr=@F_BANK_ID and [Configuration]='<profiling>' and TXM_T_DATE<=@dday
	group by userbk_nr,txm_f_customer_id) alts
on alts.UserBank_id=cl.UserBank_id and alts.Customer_id=cl.Customer_id
left join
	(select y0.UserBank_id,y0.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(y0.ProductCode_cd as varchar(4000)) + '}'),',') within group (order by y0.ProductCode_cd),1,4000) as varchar(4000)) as ProductCode_cd_list
	from 
		prdcd y0
	group by y0.UserBank_id,y0.Customer_id) prd_cd
on cl.UserBank_id=prd_cd.UserBank_id and cl.Customer_id=prd_cd.Customer_id
left join
	(select y0.UserBank_id,y0.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(y0.ProductCode as varchar(4000)) + '}'),',') within group (order by y0.ProductCode_cd),1,4000) as varchar(4000)) as ProductCode_list
	from 
		prdcd y0
	group by y0.UserBank_id,y0.Customer_id) prd_
on cl.UserBank_id=prd_.UserBank_id and cl.Customer_id=prd_.Customer_id
left join
	(select y1.UserBank_id,y1.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(y1.AccountType_cd as varchar(4000)) + '}'),',') within group (order by y1.AccountType_cd),1,4000) as varchar(4000)) as AccountType_cd_list
	from 
		acntp y1
	group by y1.UserBank_id,y1.Customer_id) actp_cd
on cl.UserBank_id=actp_cd.UserBank_id and cl.Customer_id=actp_cd.Customer_id
left join
	(select y1.UserBank_id,y1.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(y1.AccountType as varchar(4000)) + '}'),',') within group (order by y1.AccountType_cd),1,4000) as varchar(4000)) as AccountType_list
	from 
		acntp y1
	group by y1.UserBank_id,y1.Customer_id) actp_
on cl.UserBank_id=actp_.UserBank_id and cl.Customer_id=actp_.Customer_id
left join
	(select y2.UserBank_id,y2.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(y2.AccountBlockType_cd as varchar(4000)) + '}'),',') within group (order by y2.AccountBlockType_cd),1,4000) as varchar(4000)) as AccountBlockType_cd_list
	from 
		acnbl y2
	group by y2.UserBank_id,y2.Customer_id) acbl_cd
on cl.UserBank_id=acbl_cd.UserBank_id and cl.Customer_id=acbl_cd.Customer_id
left join
	(select y2.UserBank_id,y2.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(y2.AccountBlockType as varchar(4000)) + '}'),',') within group (order by y2.AccountBlockType_cd),1,4000) as varchar(4000)) as AccountBlockType_list
	from 
		acnbl y2
	group by y2.UserBank_id,y2.Customer_id) acbl_
on cl.UserBank_id=acbl_.UserBank_id and cl.Customer_id=acbl_.Customer_id
left join
	FAF.FA_CUSTOMERS agnt
on cl.UserBank_id=agnt.UserBank_id and agnt.CustomerCategory_cd='V' 
	and (cl.CustomerEMail=agnt.CustomerEMail or cl.CustomerPhoneNumber=agnt.CustomerPhoneNumber)
/*
left join
	FAF.FA_BHV_ClientStat clst
on clst.UserBank_id=cl.UserBank_id and clst.Customer_id=cl.Customer_id and clst.NameMonth = @NamMonth
left join
	(select UserBank_id,Customer_id,count(*) as cnt_month,max(meantop_W_AmountCHF) as max_meantop_W_AmountCHF,max(Risk) as Risk,
		max(BHVCluster) as prev_BHVCluster,max(max_day_sum_Amount) as prev_max_day_sum_Amount,
		max(N_month_trans) as prev_max_N_month_trans,max(sum_Amount) as prev_max_sum_Amount,
		max(sum_Inflow) as prev_max_sum_Inflow,max(sum_Outflow) as prev_max_sum_Outflow,
		max(max_Amount) as max_Amount
	from FAF.FA_BHV_ClientStat where UserBank_id=@F_BANK_ID and NameMonth>=@NamMonth2 and NameMonth<=@NamMonth 
	group by UserBank_id,Customer_id) as clst1
on clst1.UserBank_id=cl.UserBank_id and clst1.Customer_id=cl.Customer_id
left join
	(select UserBank_id,Customer_id,max(max_Saldo) as max_Saldo,
		sum(sum_Saldo/cnt_days)/count(*) as avg_Saldo
	from FAF.FA_BHV_ClientStat_d 
	where UserBank_id=@F_BANK_ID and NameMonth>=@NamMonth2 and NameMonth<=@NamMonth 
	group by UserBank_id,Customer_id) as sld1
on sld1.UserBank_id=cl.UserBank_id and sld1.Customer_id=cl.Customer_id
left join
	FAF.FA_BHV_Thresholds thr
on thr.UserBank_id=clst.UserBank_id and thr.ClientType=clst.ClientType and thr.ClusterID=clst.ClusterID and thr.BHVCluster=clst1.prev_BHVCluster and thr.NameMonth = @NamMonth
*/
where cl.UserBank_id=@F_BANK_ID and cl.Profile_code=0 and isnull(cl.Bank_flag,0)=0 and cl.CustomerOpeningDate<=@dt 
	and lower(cl.CustomerStatus_cd) in ('a','aktiv') -- bank-specific constant
	--filter to decrease the records number
	and (datediff(day,cl.CustomerOpeningDate,@dt)<=@TW
		or datediff(day,cl.CustomerLastChangeDate,@dt)<=@TW
		or datediff(day,ac_all.max_SaldoDate,@dt)<=@TW and (ac_all.TOTAL_SALDO>=50000 or sld_m1.TOTAL_SALDO>=50000)
		or datediff(day,convert(date,cast(alts.max_T_DATE as varchar(50)),112),@dt)<=@TW)
order by cl.Customer_id;

