declare @data datetime
declare @data_start datetime
set @data='20131101'
set @data_start='20131001'
declare @inspect_age table(age int,pol varchar(1))
insert into @inspect_age
	select
	AGE,
	case sex
		when 1 then 'м'
		when 0 then 'ж'
	end as POL
	from DISP_INSPECT
	where IsProfOsm=0
group by age,sex
order by AGE asc
;with zastr as
(
 select  r.pol, r.pr_rab, r.registr_id, r.datar, r.datr from polis
 left join registr r on polis.registr_id=r.registr_id
 where r.nuch is not null
 and
 (polis.datk>@data or polis.datk is NULL)
 and
 (polis.smo_id is not null or polis.smo_ino_id is not null)
 and
 (r.datar>@data or r.datar is null)
),
t as (
select
	REGISTR.POL,
	registr.PR_RAB,
	count(distinct case when inspect.age = (year(@data_start) - year(REGISTR.DATR)) then registr.registr_id end) as podl_disp,
	count(distinct case when inspect.age is not null then registr.registr_id end) as podl_disp_per,
	count(distinct REGISTR.REGISTR_ID) as cnt,
	sum(usluga_price.price) as vsego_schetov,
	COUNT(distinct case when ODISPKARTA.GRAFIK_ID = GRAFIK.GRAFIK_ID then GRAFIK.GRAFIK_ID end) as vsego_schetov_stage1,
	COUNT(distinct case when ODISPKARTA.GRAFIK2_ID = GRAFIK.GRAFIK_ID then GRAFIK.GRAFIK_ID end) as vsego_schetov_stage2,
	sum(case when ODISPKARTA.GRAFIK_ID = GRAFIK.GRAFIK_ID then usluga_price.price else 0 end) as vsego_schetov_stage1_rub,
	sum(case when ODISPKARTA.GRAFIK2_ID = GRAFIK.GRAFIK_ID then usluga_price.price else 0 end) as vsego_schetov_stage2_rub,
	sum(case when sg.PRICH is null then usluga_price.price else 0 end) as opl_vsego,
	COUNT(distinct case when ODISPKARTA.GRAFIK_ID = GRAFIK.GRAFIK_ID and sg.PRICH is null then GRAFIK.GRAFIK_ID end) as opl_stg1_cnt,
	COUNT(distinct case when ODISPKARTA.GRAFIK2_ID = GRAFIK.GRAFIK_ID and sg.PRICH is null then GRAFIK.GRAFIK_ID end) as opl_stg2_cnt,
	sum(case when ODISPKARTA.GRAFIK_ID = GRAFIK.GRAFIK_ID and sg.PRICH is null then usluga_price.price else 0 end) as opl_stg1,
	sum(case when ODISPKARTA.GRAFIK2_ID = GRAFIK.GRAFIK_ID and sg.PRICH is null then usluga_price.price else 0 end) as opl_stg2,
	COUNT(distinct case when ODISPKARTA.GRAFIK2_ID = GRAFIK.GRAFIK_ID then GRAFIK.GRAFIK_ID end) as st1_to_st2,
  COUNT(distinct case when odispres.gr_sdor='1' then REGISTR.REGISTR_ID end) as gr_sdor1,
  COUNT(distinct case when odispres.gr_sdor='2' then REGISTR.REGISTR_ID end) as gr_sdor2,
  COUNT(distinct case when odispres.gr_sdor='3' then REGISTR.REGISTR_ID end) as gr_sdor3

from zastr as registr
 left join ODISPKARTA on odispkarta.registr_id = registr.registr_id and odispkarta.is_prof = 0
 left join ODISPRES on odispres.odisp_id = odispkarta.odisp_id
 left join GRAFIK on GRAFIK.GRAFIK_ID in (ODISPKARTA.GRAFIK_ID,ODISPKARTA.GRAFIK2_ID)
 left join USLUGI on USLUGI.GRAFIK_ID=GRAFIK.GRAFIK_ID
 left join usluga_price on usluga_price.usluga_id=USLUGI.USLUGI_ID
 left join SCHGRAFIK sg on sg.GRAFIK_id=grafik.grafik_id
 outer apply (
				select top 1 age from @inspect_age as inspect  where
				age between
					(YEAR(@data_start) - YEAR(REGISTR.DATR))
					and
					(YEAR(@data) - YEAR(REGISTR.DATR))
				and inspect.pol = REGISTR.POL
			) inspect
 left join schet s on s.sch_id=sg.sch_id
where
1=1

and GRAFIK.LPU_ID = 245 and s.D_INPUT between '20131001' and '20131101' 
and grafik.PRICEGROUP_ID = 135 
and ((sg.PAYER_ID is not null and sg.PRICH is null))
--and dbo.Get_Age(registr.DATR, grafik.D_PRIEM) >= 18 
group by REGISTR.POL,registr.PR_RAB
),
lines (indx,gr_name) as(
  select 1, 'Всего'
  union all
  select 2, 'мужчины'
  union all
  select 3, 'женщины'
  union all
  select 4, 'Работающих граждан из них:'
  union all
  select 5, 'мужчины'
  union all
  select 6, 'женщины'
  union all
  select 7, 'Неработающих граждан из них:'
  union all
  select 8, 'мужчины'
  union all
  select 9, 'женщины'
)
select
	lines.gr_name,
	sum(t.cnt) as cnt,
	sum(t.podl_disp)	as podl_disp,
	sum(t.podl_disp_per) as podl_disp_per,
	round(sum(t.vsego_schetov)/1000,2) as vsego_schetov,
	SUM(t.vsego_schetov_stage1) as vsego_schetov_stage1,
	round(SUM(t.vsego_schetov_stage1_rub)/1000,2) as vsego_schetov_stage1_rub,
	SUM(t.vsego_schetov_stage2) as vsego_schetov_stage2,
	round(SUM(t.vsego_schetov_stage2_rub)/1000,2) as vsego_schetov_stage2_rub,
	round(SUM(t.opl_vsego)/1000,2) as opl_vsego,
	sum(opl_stg1_cnt) as opl_stg1_cnt,
	sum(opl_stg2_cnt) as opl_stg2_cnt,
	round(SUM(t.opl_stg1)/1000,2) as opl_stg1,
	round(SUM(t.opl_stg2)/1000,2) as opl_stg2,
	SUM(t.st1_to_st2) as st1_to_st2,
	SUM(t.gr_sdor1) as gr_sdor1,
	SUM(t.gr_sdor2) as gr_sdor2,
	SUM(t.gr_sdor3) as gr_sdor3
from lines
left join t on
lines.indx=1
or
(lines.indx=2 and t.POL='м')
or
(lines.indx=3 and t.POL='ж')
or
(lines.indx=4 and t.pr_rab=1)
or
(lines.indx=5 and t.pr_rab=1 and t.POL='м')
or
(lines.indx=6 and t.pr_rab=1 and t.POL='ж')
or
(lines.indx=7 and t.pr_rab=0)
or
(lines.indx=8 and t.pr_rab=0 and t.POL='м')
or
(lines.indx=9 and t.pr_rab=0 and t.POL='ж')
group by lines.indx,lines.gr_name
order by lines.indx asc;