ALTER procedure [dbo].[mn_paydms]
@number int,
@fio varchar( 50 ),
@syear int,
@eyear int
as
begin
select 
--sum(case when year(datepay)=datepay then 1 else 0 end) as hosp35,

@number as number, dbo.fio1(insur.insured_id)as fio, insur.inn,	p.startdate, p.enddate, p.spolis, p.npolis, sum(payment) as payment, 
pp.payform, dbo.NumPhrase(sum(payment), 1 ) as strpayment, year(pp.datepay) as datepay , p.n_dog, pp.datepay as datepay2, ' 'as fioz, ' 'as ids, ' ' as idz
from insured insur inner join polis p on insur.insured_id=p.insured_id
		           inner join polispay pp on p.polis_id=pp.polis_id
			       inner join contract c on p.contract_id=c.contract_id
		     
where  1  =  1 
	and @fio=.dbo.fio1(insur.insured_id)
	--and insur.insured_id=@insured_id
	and pp.payertype= 2 
	and year(pp.datepay) between @syear and @eyear
	and c.type= 3 
	-- учитывать только страховой взнос по факту type=2
	and pp.type in ( 2 , 19 ) --Факт + возврат

    group by year(pp.datepay), p.n_dog, pp.payform, pp.datepay, insur.insured_id, insur.inn, p.startdate, p.enddate, p.spolis, p.npolis

union

select 

@number as number,dbo.fio1(ci.insurant_id) as fio, null as inn, date_b as startdate, date_e as enddate, p.spolis, p.npolis, 
sum(cpt.payment) as payment, cpt.payform, dbo.NumPhrase(sum(cpt.payment), 1 )as strpayment, year(cpt.datepay) as datepay, c.n_dog, cpt.datepay as datepay2,

ltrim(rtrim(i.surname)+' '+rtrim(i.name)+' '+rtrim(i.secname))as fioz, i.insured_id as idz, ci.insurant_id as ids

from contrint ci inner join contract c on ci.contract_id=c.contract_id
				 inner join contrpmt cpt on cpt.contract_id=c.contract_id, polis p, insured i

where 
--ci.insurant_id=@insured_id
      @fio=.dbo.fio1(ci.insurant_id)
	 and year(cpt.datepay) between @syear and @eyear
	 and cpt.payertype= 2 
	 and c.type <> 3 
	 and cpt.type in ( 2 , 19 )
     and p.contract_id=c.contract_id
     and p.insured_id=i.insured_id
     group by year(cpt.datepay), c.n_dog, cpt.payform, p.spolis, p.npolis, cpt.datepay, i.surname, i.name, i.secname, i.insured_id, ci.insurant_id, date_b, date_e



end