select
	lpu.lpu_ogrn AS lpu_ogrn,
	lpu.lpu_nick AS lpu_nick,
	ls.lpusection_id,
	ls.lpusection_name,
	addr.address_address,
	ls.lpusectionprofile_code,
	ls.lpusectionprofile_name,
	case 
		when lsp.lpusectionbedprofile_id = '3138' then '84_1' 
		when lsp.lpusectionbedprofile_id = '3178' then '3_1' 
		when lsp.lpusectionbedprofile_id = '3069' then '24_1'
		when lsp.lpusectionbedprofile_code = '27' then '74_1'
		else lspfed.lpusectionbedprofile_code
	end LpuSectionBedProfile_Code,  
	lspfed.lpusectionbedprofile_name,
	lsp.lpusectionbedprofile_code as bedprofile_code,
	lsp.lpusectionbedprofile_name as bedprofile_name,
	coalesce(lss.lpusectionbedstate_fact,0) as fact,
	coalesce(lss.lpusectionbedstate_malefact,0) as factmale,
	coalesce(lss.lpusectionbedstate_femalefact,0) as factfemale,
	case when lsp.lpusectionbedprofile_ischild = 2 then coalesce(lss.lpusectionbedstate_fact,0) else 0 end factchild,
	case 
		when coalesce(lsp.lpusectionbedprofile_ischild,1)=1 
		then coalesce(lss.lpusectionbedstate_fact,0)-coalesce(lss.lpusectionbedstate_malefact,0)-coalesce(lss.lpusectionbedstate_femalefact,0) 
		else 0 
	end as factob,
	es.zan as zan,----
    (coalesce(lss.lpusectionbedstate_fact,0) - es.zan) AS Svob,
    CASE WHEN (coalesce(lss.lpusectionbedstate_malefact,0) - es.Male)>0 THEN coalesce(lss.lpusectionbedstate_malefact,0) - es.Male ELSE 0 END as SvobMale,
    CASE WHEN (coalesce(lss.lpusectionbedstate_femalefact,0) - es.Female)>0 THEN coalesce(lss.lpusectionbedstate_femalefact,0)  - es.Female ELSE 0 END as SvobFeMale,
    CASE WHEN (case when lsp.lpusectionbedprofile_ischild = 2 then coalesce(lss.lpusectionbedstate_fact,0) else 0 end - es.Child)>0 THEN case when lsp.lpusectionbedprofile_ischild = 2 then coalesce(lss.lpusectionbedstate_fact,0) else 0 end - es.Child ELSE 0 END as SvobChild,
	((case when coalesce(lsp.lpusectionbedprofile_ischild,1)=1 then coalesce(lss.lpusectionbedstate_fact,0)-coalesce(lss.lpusectionbedstate_malefact,0)-coalesce(lss.lpusectionbedstate_femalefact,0) else 0 end) - 
	((CASE WHEN (es.Male - coalesce(lss.lpusectionbedstate_malefact,0))>0 THEN es.Male - coalesce(lss.lpusectionbedstate_malefact,0) ELSE 0 END) + 
	 (CASE WHEN (es.Female - coalesce(lss.lpusectionbedstate_femalefact,0))>0 THEN es.Female - coalesce(lss.lpusectionbedstate_femalefact,0) ELSE 0 END))) as SvobOb 
from dbo.v_lpusectionbedstate as lss
	inner join fed.v_lpusectionbedprofilelink 	as lsplink 	on lsplink.lpusectionbedprofilelink_id=lss.lpusectionbedprofileLink_fedid
	inner join dbo.v_lpusectionbedprofile 		as lsp 		on lsp.lpusectionbedprofile_id=lsplink.lpusectionbedprofile_id
	inner join fed.v_lpusectionbedprofile 		as lspfed 	on lsplink.lpusectionbedprofile_fedid=lspfed.lpusectionbedprofile_id
	inner join dbo.v_lpusection 				as ls		on lss.lpusection_id=ls.lpusection_id
	inner join dbo.lpubuilding 					as b 		on ls.lpubuilding_id=b.lpubuilding_id
	left join dbo.address 						as addr 	on addr.address_id=b.address_id
	inner join dbo.v_lpu 						as lpu 		on ls.lpu_id=lpu.lpu_id
	left join fed.passporttoken 				as pt 		on lpu.lpu_id=pt.lpu_id
	inner join lateral(
		select 
			count(evns.evnsection_id) as zan,
			coalesce(sum(case when ps.sex_id=1 and ps.person_age>=18 then 1 else 0 end),0) as male,
			coalesce(sum(case when ps.sex_id=2 and ps.person_age>=18 then 1 else 0 end),0) as female,
			coalesce(sum(case when ps.person_age<18 then 1 else 0 end),0) as child
		from dbo.v_evnsection as evns
			inner join dbo.v_personstate_all as ps on evns.person_id=ps.person_id
		where evns.lpu_id=lpu.lpu_id and evns.lpusection_id=lss.lpusection_id 
			and evns.evnsection_disdate is null and coalesce(evns.evnsection_ispriem,1)=1
	) as es on true
where lss.lpusectionbedstate_enddate is null and lsp.lpusectionbedprofile_enddt is null and lpu.lpu_enddate is null 
group by
	lpu.lpu_ogrn,
	lpu.lpu_nick,
	ls.lpusection_id,
	ls.lpusection_name,
	addr.address_address,
	ls.lpusectionprofile_code,
	ls.lpusectionprofile_name,
	case 
		when lsp.lpusectionbedprofile_id = '3138' then '84_1' 
		when lsp.lpusectionbedprofile_id = '3178' then '3_1' 
		when lsp.lpusectionbedprofile_id = '3069' then '24_1'
		when lsp.lpusectionbedprofile_code = '27' then '74_1'
		else lspfed.lpusectionbedprofile_code
	end ,  
	lspfed.lpusectionbedprofile_name,
	lsp.lpusectionbedprofile_code,
	lsp.lpusectionbedprofile_name,
	coalesce(lss.lpusectionbedstate_fact,0),
	coalesce(lss.lpusectionbedstate_malefact,0),
	coalesce(lss.lpusectionbedstate_femalefact,0),
	case when lsp.lpusectionbedprofile_ischild = 2 then coalesce(lss.lpusectionbedstate_fact,0) else 0 end,
	case 
		when coalesce(lsp.lpusectionbedprofile_ischild,1)=1 
		then coalesce(lss.lpusectionbedstate_fact,0)-coalesce(lss.lpusectionbedstate_malefact,0)-coalesce(lss.lpusectionbedstate_femalefact,0) 
		else 0 
	end,
	es.zan,
    (coalesce(lss.lpusectionbedstate_fact,0) - es.zan),
    CASE WHEN (coalesce(lss.lpusectionbedstate_malefact,0) - es.Male)>0 THEN coalesce(lss.lpusectionbedstate_malefact,0) - es.Male ELSE 0 END,
    CASE WHEN (coalesce(lss.lpusectionbedstate_femalefact,0) - es.Female)>0 THEN coalesce(lss.lpusectionbedstate_femalefact,0)  - es.Female ELSE 0 END,
    CASE WHEN (case when lsp.lpusectionbedprofile_ischild = 2 then coalesce(lss.lpusectionbedstate_fact,0) else 0 end - es.Child)>0 THEN case when lsp.lpusectionbedprofile_ischild = 2 then coalesce(lss.lpusectionbedstate_fact,0) else 0 end - es.Child ELSE 0 END,
	((case when coalesce(lsp.lpusectionbedprofile_ischild,1)=1 then coalesce(lss.lpusectionbedstate_fact,0)-coalesce(lss.lpusectionbedstate_malefact,0)-coalesce(lss.lpusectionbedstate_femalefact,0) else 0 end) - 
	((CASE WHEN (es.Male - coalesce(lss.lpusectionbedstate_malefact,0))>0 THEN es.Male - coalesce(lss.lpusectionbedstate_malefact,0) ELSE 0 END) + 
	 (CASE WHEN (es.Female - coalesce(lss.lpusectionbedstate_femalefact,0))>0 THEN es.Female - coalesce(lss.lpusectionbedstate_femalefact,0) ELSE 0 END)));

