select ROW_NUMBER() OVER(order by id_srv) num, id_srv, name, saldo, curr_value, norm, formula, val, nach, pere, nach*disc/ 100  as disc, nach+pere-(nach*disc/ 100 ) as itogo, pay, saldo+nach+pere-(nach*disc/ 100 )-pay as saldo_out, curr_pay =
case
	when nach+pere-(nach*disc/ 100 ) - pay <=  0  then  0 
	when nach+pere-(nach*disc/ 100 ) - pay >  0  then nach+pere-(nach*disc/ 100 ) - pay
end
from
(select id_srv, name, saldo, curr_value, norm, formula, val, nach, pere, disc, isnull(pay_sum, 0 ) as pay from
(select id_service, sum(pay_sum) as pay_sum from mb_pays where id_ls =  353356  and createdate >= (select max(closingdate) as cd from mb_period) and Createdate <= GETDATE() group by id_service) as _pays
right join
(select id_srv, name, saldo, curr_value, norm, formula, val, nach, pere, ISNULL(value, 0 ) as disc from
(select id_service, value from mb_ls_disc where begin_date <= (select max(closingdate) as cd from mb_period) and (end_date is null or end_date >= GETDATE())) _disc
right join
(select id_srv, name, saldo, curr_value, norm, formula, val, nach, ISNULL(pere, 0 ) as pere from
(select id_service, SUM(value) as pere from mb_ls_pere
where id_ls =  353356  and Createdate >= (select max(closingdate) as cd from mb_period) and Createdate <= GETDATE() group by id_service) as _pere
right join
(select id_srv, name, saldo, curr_value, norm, formula, val, curr_value * val as nach from
(select id_srv, name, saldo, curr_value, norm, formula, val =
	case
		when norm =  0  then ISNULL(val, 0 )
		when norm =  1  and formula is null OR formula = '1' then  1 
		when norm =  1  and formula = 'prop' then (select prop from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'prog' then (select prog from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 's_all' then (select s_all from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 's_otap' then (select s_otap from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv1+s_all' and (select priv from mb_flats where id_flat =  315924 ) =  1  then (select s_all from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv1+s_all' and (select priv from mb_flats where id_flat =  315924 ) =  0  then  0 
		when norm =  1  and formula = 'priv1+s_otap' and (select priv from mb_flats where id_flat =  315924 ) =  1  then (select s_otap from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv1+s_otap' and (select priv from mb_flats where id_flat =  315924 ) =  0  then  0 
		when norm =  1  and formula = 'priv1+prop' and (select priv from mb_flats where id_flat =  315924 ) =  1  then (select prop from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv1+prop' and (select priv from mb_flats where id_flat =  315924 ) =  0  then  0 
		when norm =  1  and formula = 'priv1+prog' and (select priv from mb_flats where id_flat =  315924 ) =  1  then (select prog from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv1+prog' and (select priv from mb_flats where id_flat =  315924 ) =  0  then  0 
		when norm =  1  and formula = 'priv0+prop' and (select priv from mb_flats where id_flat =  315924 ) =  0  then (select prop from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv0+prop' and (select priv from mb_flats where id_flat =  315924 ) =  1  then  0 
		when norm =  1  and formula = 'priv0+prog' and (select priv from mb_flats where id_flat =  315924 ) =  0  then (select prog from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv0+prop' and (select priv from mb_flats where id_flat =  315924 ) =  1  then  0 
		when norm =  1  and formula = 'priv0+s_all' and (select priv from mb_flats where id_flat =  315924 ) =  0  then (select s_all from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv0+s_all' and (select priv from mb_flats where id_flat =  315924 ) =  1  then  0 
		when norm =  1  and formula = 'priv0+s_otap' and (select priv from mb_flats where id_flat =  315924 ) =  0  then (select s_otap from mb_flats where id_flat =  315924 )
		when norm =  1  and formula = 'priv0+s_otap' and (select priv from mb_flats where id_flat =  315924 ) =  1  then  0 
	end
from
(select id_srv,name,saldo,curr_value, norm, formula from
(select id_srv, name, saldo, id_tar, formula from
(select id_srv, name, ISNULL(saldo_in, 0 ) as saldo, formula from
(select id as id_srv, name, formula from
(select id, name, formula from mb_service where begin_date <= GETDATE() and (end_date is null OR end_date >= GETDATE())) usl
right join
(select distinct id_service from mb_house_usl where id_house =  217807  AND begin_date <= GETDATE() and (end_date is null or end_date >= GETDATE())) house_srv
on usl.id = house_srv.id_service) name_usl
left join
(select id_service, saldo_in from mb_ls_nach
where createdate >= (select max(closingdate) as cd from mb_period) and createdate <= GETDATE() and id_ls =  353356 ) as sld on id_srv = id_service) as idt
left join (select id_tar, id_service from mb_ls_tar where begin_date <= GETDATE() and (end_date is null or end_date >= GETDATE()) and id_ls =  353356 ) as idtar on id_srv = id_service) as l

left join

(select id, id_service, curr_value, norm
from mb_tariff where begin_date <= GETDATE() and (end_date is null or end_date >= getdate())) as valtar
on id = id_tar) as t

left join

(select id_service, SUM(curr_value-old_value) as val from 
(select minmax_cv.id_ls, minmax_cv.id_service, minmax_cv.curr_value, old_value = 
	case 
	when old.old_value is null and minmax_cv.curr_value >=  0  then minmax_cv.min_curr
	when old.old_value is not null then ISNULL(old.old_value, 0 ) 
	end 
from
(select new.id_ls, new.id_service, new.id_cnt, new.curr_value, cv as min_curr from
(select id_service, id_ls, id_cnt, max(curr_value) as curr_value 
from mb_ls_cnt where id_ls =  353356  and CreateDate <= GETDATE()
and createdate >= (select max(closingdate) from mb_period)
group by id_service, id_ls, id_cnt) as new
left join
(select id_service, id_ls, id_cnt, min(curr_value) as cv
from mb_ls_cnt where id_ls =  353356  and CreateDate <= GETDATE()
and createdate >= (select max(closingdate) from mb_period)
group by id_service, id_ls, id_cnt) min_cv
on new.id_ls = min_cv.id_ls AND new.id_service = min_cv.id_service and new.id_cnt = min_cv.id_cnt) as minmax_cv
left join 
(select id_service, id_ls, id_cnt, max(curr_value) as old_value 
from mb_ls_cnt where id_ls =  353356  and CreateDate < (select max(closingdate) from mb_period)
group by id_service, id_ls, id_cnt) as old
on minmax_cv.id_ls = old.id_ls AND minmax_cv.id_service = old.id_service and minmax_cv.id_cnt = old.id_cnt) as both group by id_service) as cnts on cnts.id_service = t.id_srv) as val) as nach on _pere.id_service = nach.id_srv) as p on p.id_srv = _disc.id_service)
as t on t.id_srv = _pays.id_service) as x where curr_value is not null