use kv2021

create table #adr (kod_adres bigint, 				
                   p varchar(500))				
				   --drop table  #adr
				
declare @code tinyint, @kod_adres bigint, @p varchar(500)				
declare adr cursor forward_only				
for				
				
select  kod_adres				
from spr..contact				
where stel like '(9%'				
and kod_adres in (				
select kod_adres				
from nach_t 				
where kod_postav in (select code from spr..postav where name like '%ГУП%' or name like '%тикси%комм%')				
group by kod_adres				
having sum(summa)>0) 				
group by kod_adres				
				
open adr				
fetch next from adr into  @kod_adres--, @p				
while @@FETCH_STATUS=0				
begin				
				
insert into #adr				
select  top 1 @kod_adres, stel				
from spr..contact				
where kod_adres=@kod_adres	and	 stel like '(9%'			
group by stel				
				
				
				
fetch next from adr into  @kod_adres--,  @p				
end				
close adr				
deallocate adr				
				
declare @contacts table (region varchar(100), code bigint,phone varchar(20),phone_kassa varchar(10))	
			 
Insert @contacts				
select v.region, a.kod_adres, '+7'+Replace(Replace(Replace(p,'(',''),')',''),'-','') as p, Right(replace(tel,'-',''),5) as kassa
from #adr a inner join
spr..vadres v on v.code=a.kod_adres inner join
spr..org o on v.code_region=o.region
where kod_adres in (
select kod_adres
from nach_t 
where kod_postav in (select code from spr..postav where name like '%ГУП%' or name like '%тикси%комм%')
group by kod_adres
having sum(summa)>0) 
and v.region not like '%г. Якутск%'
group by v.region, a.kod_adres, p, tel
order by v.region


drop table #adr



-- поулчим только те телефоны, у которых есть просрочка
declare @mes tinyint

select @mes=mes
from spr..year
where tek=1

select a.region Район, round(sum(saldo), 2) Сальдо, round(sum(kt), 2) Кт, round(sum(dt), 2) Дт, round(sum(tek), 2) Текущая, round(sum(pos), 2) Просроченная,
cnts.phone, cnts.phone_kassa
from (select kod_adres, saldo
	, case when saldo < 0 then saldo * -1 else 0 end as kt
	, case when saldo > 0 then saldo else 0 end as dt
	, case when saldo > 0 then case when nach < saldo then nach else saldo end else 0 end as tek
	, case when saldo > 0 then saldo else 0 end - case when saldo > 0 then case when nach < saldo then nach else saldo end else 0 end as pos
	from (select kod_adres, sum(case when vid = 0 then summa else 0 end) as saldo, sum(case when vid = 1 then summa else 0 end) as nach
		from (select kod_adres, cast(sum(summa * 0.01) as float) as summa, 0 as vid
			from kv2021..saldo_t
			where kod_postav in (select code from spr..postav where name like '%гуп%жкх%')
			group by kod_adres
			union all
			select kod_adres, cast(sum(summa * 0.01) as float) as summa, 1 as vid
			from kv2021..nach_a
			where kod_postav in (select code from spr..postav where name like '%гуп%жкх%') and mes in (@mes - 2, @mes - 1)
			group by kod_adres) a
		group by kod_adres) a) u
inner join spr..vAdres a on a.code = u.kod_adres
Inner Join @contacts cnts On cnts.code=a.code
Where pos>0
group by a.region, cnts.phone, cnts.phone_kassa
order by a.region