select	DISTINCT 
	COND.CT_CONTRACT_ID,
	COND.CONTRACT_NUM,
	COND.CONTRACT_DTM,
	coalesce( CUR.CURRENCY_ID,3),
	LNK.DIVISION_ID,
	case when MULT.CT_CONTRACT_ID is null then -1 else lnk.CNTR_KIS_ID end,
	V_RENT_MM.ACCOUNT_ID,
	LNK.BEGIN_DTM,
	LNK.END_DTM,
	V_RENT_MM.RENT_SUM_MAX,
	LNK.RENT_AREA,
	LNK.PAYMENT_FREQUENCY,
	STATUS.CONTRACT_DTM_FINISH,
	#HYP.SESSION_N 
from	((((DEV_DB_ODI.T_RENT_CONTRACT_COND_SNST    COND INNER JOIN DEV_DB_ODI.T_RENT_OBJECT_LNK_SNST    LNK ON COND.RENT_CONTRACT_COND_ID=LNK.RENT_CONTRACT_COND_ID and 
cond.IS_VERIFIED = 1 and 
cond.DELETION_MARK = 0) LEFT OUTER JOIN DEV_DB_ODI.T_RENT_MULTIMEMB_CT_SNST    MULT ON COND.CT_CONTRACT_ID=MULT.CT_CONTRACT_ID) LEFT OUTER JOIN (

select 	
	RENT_CT_STATUS_LNK_SNST.CT_CONTRACT_ID    CT_CONTRACT_ID,	min(RENT_CT_STATUS_LNK_SNST.CONTRACT_DTM)    CONTRACT_DTM_FINISH 
from	DEV_DB_ODI.T_RENT_CT_STATUS_LNK_SNST   RENT_CT_STATUS_LNK_SNST
where	(1=1)

And (RENT_CT_STATUS_LNK_SNST.IS_ACTUAL in ('Расторгнут','ОкончившийСрокДействия'))
Group By RENT_CT_STATUS_LNK_SNST.CT_CONTRACT_ID

)    STATUS ON COND.CT_CONTRACT_ID=STATUS.CT_CONTRACT_ID) LEFT OUTER JOIN DEV_DB_ODI.T_RENT_ADDITIONAL_INFO_LNK_SNST    INF ON COND.RENT_CONTRACT_COND_ID=INF.RENT_CONTRACT_COND_ID) LEFT OUTER JOIN DEV_DB_ODI.T_CURRENCY_SNST    CUR ON coalesce(LNK.CURRENCY_ID,3) = CUR.CURRENCY_ID, (

select 	
	V_R.DIVISION_ID    DIVISION_ID,	max(coalesce(NULLIF(V_R.RENT_SUM, 0),NULLIF(V_R.RENT_SUM_MAX, 0),NULLIF(V_R.RENT_SUM_MIN, 0)))    RENT_SUM_MAX,	3402986    ACCOUNT_ID,	V_R.CNTR_KIS_ID    CNTR_KIS_ID,	V_R.RENT_CONTRACT_COND_ID    RENT_CONTRACT_COND_ID
from	DEV_DB_ODI.T_RENT_TURNOVER_FEE_SNST   V_R, DEV_DB_HYP.V_MONTHS   V_MONTHS
where	(1=1)

And (V_MONTHS.MONTH_ID between #HYP.BEGIN_MONTH and #HYP.END_MONTH)
 And (V_MONTHS.MONTH_ID between to_month_id(cast(V_R.BEGIN_DTM as date)) and to_month_id(cast(V_R.END_DTM as date)))
 And (V_R.CALC_TYPE='ФиксированнаяСумма')
Group By V_R.DIVISION_ID,
 V_R.CNTR_KIS_ID,
 V_R.RENT_CONTRACT_COND_ID


UNION
select 	
	V_R.DIVISION_ID    DIVISION_ID,	min(coalesce(NULLIF(V_R.RENT_SUM, 0),NULLIF(V_R.RENT_SUM_MAX, 0),NULLIF(V_R.RENT_SUM_MIN, 0)))    RENT_SUM_MAX,	3402985    ACCOUNT_ID,	V_R.CNTR_KIS_ID    CNTR_KIS_ID,	V_R.RENT_CONTRACT_COND_ID    RENT_CONTRACT_COND_ID
from	DEV_DB_ODI.T_RENT_TURNOVER_FEE_SNST   V_R, DEV_DB_HYP.V_MONTHS   V_MONTHS
where	(1=1)

And (V_MONTHS.MONTH_ID between #HYP.BEGIN_MONTH and #HYP.END_MONTH)
 And (V_MONTHS.MONTH_ID between to_month_id(cast(V_R.BEGIN_DTM as date)) and to_month_id(cast(V_R.END_DTM as date)))
 And (V_R.CALC_TYPE='ФиксированнаяСумма')
Group By V_R.DIVISION_ID,
 V_R.CNTR_KIS_ID,
 V_R.RENT_CONTRACT_COND_ID


UNION
select 	
	V_R.DIVISION_ID    DIVISION_ID,	avg(coalesce(NULLIF(V_R.RENT_SUM, 0),NULLIF(V_R.RENT_SUM_MAX, 0),NULLIF(V_R.RENT_SUM_MIN, 0)))    RENT_SUM_MAX,	3402982    ACCOUNT_ID,	V_R.CNTR_KIS_ID    CNTR_KIS_ID,	V_R.RENT_CONTRACT_COND_ID    RENT_CONTRACT_COND_ID
from	DEV_DB_ODI.T_RENT_TURNOVER_FEE_SNST   V_R, DEV_DB_HYP.V_MONTHS   V_MONTHS
where	(1=1)

And (V_R.CALC_TYPE='Процент')
 And (V_MONTHS.MONTH_ID between #HYP.BEGIN_MONTH and #HYP.END_MONTH)
 And (V_MONTHS.MONTH_ID between to_month_id(cast(V_R.BEGIN_DTM as date)) and to_month_id(cast(V_R.END_DTM as date)))
Group By V_R.DIVISION_ID,
 V_R.CNTR_KIS_ID,
 V_R.RENT_CONTRACT_COND_ID

)   V_RENT_MM 
where	(1=1)
 And (COND.RENT_CONTRACT_COND_ID=V_RENT_MM.RENT_CONTRACT_COND_ID)

select 	
	V_R.DIVISION_ID    DIVISION_ID,	max(coalesce(NULLIF(V_R.RENT_SUM, 0),NULLIF(V_R.RENT_SUM_MAX, 0),NULLIF(V_R.RENT_SUM_MIN, 0)))    RENT_SUM_MAX,	3402986    ACCOUNT_ID,	V_R.CNTR_KIS_ID    CNTR_KIS_ID,	V_R.RENT_CONTRACT_COND_ID    RENT_CONTRACT_COND_ID
from	DEV_DB_ODI.T_RENT_TURNOVER_FEE_SNST   V_R, DEV_DB_HYP.V_MONTHS   V_MONTHS
where	(1=1)

And (V_MONTHS.MONTH_ID between #HYP.BEGIN_MONTH and #HYP.END_MONTH)
 And (V_MONTHS.MONTH_ID between to_month_id(cast(V_R.BEGIN_DTM as date)) and to_month_id(cast(V_R.END_DTM as date)))
 And (V_R.CALC_TYPE='ФиксированнаяСумма')
Group By V_R.DIVISION_ID,
 V_R.CNTR_KIS_ID,
 V_R.RENT_CONTRACT_COND_ID