
select distinct
	PS.Person_id,
	LpuFrom.Lpu_Nick "Мед.организация, обратившаяся за консультацией", 
	ED.EvnDirection_setDT as "Дата обращения за консультацией"
	,rtrim(coalesce(PS.Person_SurName,'')) "Фамилия"
	,rtrim(coalesce(PS.Person_FirName,'')) "Имя"
	,rtrim(coalesce(PS.Person_SecName,'')) "Отчество"
	,PS.Person_BirthDay as "Дата рождения"
--	,coalesce(LSP.LpuSectionProfile_Code, '') as "Код профиля"
--	,ed.EvnDirection_Num "номер направления"
	,st.EvnStatus_Name "Статус"
	,case when ps.Sex_id=1 then 'М'
	when ps.Sex_id=2 then 'Ж' else '' end "пол"
	,ps.Person_Snils "СНИЛС"
	,ps.Polis_Ser "Серия полиса ОМС"
	,ps.Polis_Num "№ полиса ОМС"
	,pt.PolisType_Name "Тип полиса"
	,org_smo.org_name
	,"Диагноз основной (код по МКБ-10)"
	,"Код услуги"
	,"Дата консультации"
	,Person_Fio "ФИО врача-консультанта"
	,usl.MedPersonal_id "Код врача"
	,coalesce(LSP.LpuSectionProfile_Code, '') as "Код профиля"
	,usl.LpuSectionProfile_Name "Профиль консультации"
	,doc.Document_Ser "Серия документа, удостоверяющего личность (ДУЛ)"
	,doc.Document_Num "№ документа, удостоверяющего личность"
	,doct.DocumentType_Name "Тип документа, удостоверяющего личность"
	,doc.Document_begDate "Дата выдачи ДУЛ"
	,nat.klcountry_name  "Гражданство"
	,od.org_name "Кем выдан ДУЛ"
--  
from  
	dbo.v_Lpu LpuFrom 
	join dbo.v_EvnDirection_all ED 
		on coalesce(ED.Lpu_sid, ED.Lpu_id) = LpuFrom.Lpu_id
		and coalesce(LpuFrom.Lpu_IsTest, 1) = 1
	join dbo.v_PersonState PS 
		on PS.Person_id = ed.Person_id
	left join polis as oms_pol 
		on ps.polis_id = oms_pol.polis_id
	left join  PolisType as pt 
		on oms_pol.PolisType_id = pt.polistype_id
	left join dbo.v_LpuSectionProfile LSP 
		on LSP.LpuSectionProfile_id = ED.LpuSectionProfile_id
	left join OrgSMO as smo 
		on oms_pol.orgsmo_id = smo.orgsmo_id
	left join org as org_smo 
		on smo.org_id = org_smo.org_id
	left join V_EvnStatus st 
		on st.EvnStatus_id=ED.EvnStatus_id
	left join klcountry as nat  
		on ps.klcountry_id=nat.klcountry_id
	left join document as doc  
		on ps.document_id = doc.document_id
	left join v_OrgDep od 
		on od.orgdep_id=doc.orgdep_id
	left join documenttype as doct  
		on doc.documenttype_id = doct.documenttype_id 
	join (
					select distinct
					row_number () over (partition by  rtrim(coalesce(PS.Person_SurName,''))||' '||rtrim(coalesce(PS.Person_FirName,''))||' '||rtrim(coalesce(PS.Person_SecName,'')),PS.Person_BirthDay order by  EUT.EvnUslugaTelemed_setDT ) "Порядковый номер консультации",
				  ED.EvnDirection_setDT as "Дата обращения за консультацией",
				  rtrim(coalesce(PS.Person_SurName,''))||rtrim(coalesce(PS.Person_FirName,''))||rtrim(coalesce(PS.Person_SecName,'')) as "ФИО пациента",
				  PS.Person_BirthDay as "Дата рождения",
				  coalesce(D.Diag_Code, '') as "Диагноз основной (код по МКБ-10)",
				  coalesce(UC.UslugaComplex_Code, '') as "Код услуги",
				  ED.EvnDirection_id,
				  mp.Person_Fio, eut.MedPersonal_id,
				  EUT.EvnUslugaTelemed_setDT as "Дата консультации",
				   ed.EvnDirection_Num "номер направления",
				  coalesce(LSP.LpuSectionProfile_Code, '') as "Код профиля",
				  lsp.LpuSectionProfile_Name
				from dbo.v_Lpu LpuUtel 
				left join dbo.v_EvnUslugaTelemed EUT on coalesce(EUT.Lpu_uid, EUT.Lpu_id) = LpuUtel.Lpu_id
				-- and year(EUT.EvnUslugaTelemed_setDate)=2020
				  and coalesce(LpuUtel.Lpu_IsTest, 1) = 1
				join dbo.v_PersonState PS on PS.Person_id = EUT.Person_id
				left join dbo.v_EvnDirection_all ED on ED.EvnDirection_id = EUT.EvnDirection_id
				left join dbo.v_Lpu LpuFrom on coalesce(ED.Lpu_sid, ED.Lpu_id) = LpuFrom.Lpu_id 
				left join dbo.v_Diag D on D.Diag_id = ED.Diag_id
				left join dbo.v_LpuSectionProfile LSP on LSP.LpuSectionProfile_id = ED.LpuSectionProfile_id
				left join dbo.v_UslugaComplex UC on UC.UslugaComplex_id = EUT.UslugaComplex_id
				left join v_MedStaffFact as mp  on eut.MedPersonal_id=mp.MedPersonal_id
	) usl 
	on usl."Дата обращения за консультацией"=ED.EvnDirection_setDT 
	and usl."ФИО пациента"=rtrim(coalesce(PS.Person_SurName,''))||rtrim(coalesce(PS.Person_FirName,''))||rtrim(coalesce(PS.Person_SecName,''))
	and usl."номер направления"=ed.EvnDirection_Num
	and usl."Дата рождения"=PS.Person_BirthDay
	and usl."Код профиля"=coalesce(LSP.LpuSectionProfile_Code, '')
where   
  ED.RemoteConsultCause_id is not null
  and st.EvnStatus_id=15
--	and od.org_name like '%ОБЩЕСТВО%'
  --and year(usl."Дата обращения за консультацией")=2020
  --18.08.1948 00:00:00
--  and PS.Person_BirthDay='1948-08-18 00:00:00'
  --and PS.Person_SurName='РОМАНОВА'
 -- ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ "СТРАХОВАЯ МЕДИЦИНСКАЯ КОМПАНИЯ РЕСО-МЕД"
