if exists ( select * from sys.procedures p where p.name = 'sp_V018TypeLoad' ) DROP PROCEDURE [dbo].[sp_V018TypeLoad]
GO
CREATE PROCEDURE [dbo].[sp_V018TypeLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

select
	T.c.value('./IDHVID[1]','varchar(30)') as [IDHVID]
	,T.c.value('./HVIDNAME[1]','varchar(1000)') as [HVIDNAME]
	,case when T.c.value('./DATEBEG[1]','varchar(40)')='' then null else convert(datetime,T.c.value('./DATEBEG[1]','varchar(30)'),104) end as [DATEBEG]
	,case when T.c.value('./DATEEND[1]','varchar(40)')='' then null else convert(datetime,T.c.value('./DATEEND[1]','varchar(30)'),104) end as [DATEEND]
	into #V018
from @Xml.nodes('/packet/zap') T(c)

select aa.* into #tmp
from [IES].T_V018_VMP_TYPE t1
cross apply (
select top 1 * from #V018 t11 
where t1.IDHVID = t11.IDHVID 
order by datebeg desc 
) aa

update t1
set t1.HVIDNAME = aa.HVIDNAME
	,t1.DATEBEG=aa.DATEBEG
	,t1.DATEEND = aa.DATEEND
from #tmp aa
join [IES].T_V018_VMP_TYPE t1 on (aa.IDHVID = t1.IDHVID)

insert into [IES].T_V018_VMP_TYPE
select aa.IDHVID, aa.HVIDNAME, aa.DATEBEG, aa.DATEEND
from #V018 t0
left join [IES].T_V018_VMP_TYPE t1 on (t1.IDHVID = t0.IDHVID)
cross apply (select top 1 * from #V018 t11 where t11.IDHVID = t0.IDHVID order by DATEBEG desc) aa
where t1.IDHVID is null
group by aa.IDHVID, aa.HVIDNAME, aa.DATEBEG, aa.DATEEND

insert into [IES].T_V018_VMP_TYPE_PREV(V018VmpTypePrevID, V018VmpType, DATEBEG, HVIDNAME, DATEEND)
select newid(),t1.IDHVID,t1.DATEBEG,t1.HVIDNAME,t1.DATEEND
from #V018 t1
left join [IES].T_V018_VMP_TYPE_PREV t2 on (t1.IDHVID = t2.V018VmpType and t1.HVIDNAME = t2.HVIDNAME and cast(t1.DATEBEG as varchar(30)) = cast(t2.DATEBEG as varchar(30)))
join [IES].T_V018_VMP_TYPE t3 on (t3.IDHVID=t1.IDHVID)
where t2.V018VmpTypePrevID is null

update t1
set DATEEND = DATEADD(DAY, -1, aa.DATEBEG)
from ies.T_V018_VMP_TYPE_PREV t1
cross apply(select top 1 * from ies.T_V018_VMP_TYPE_PREV t10 where t1.V018VmpTypePrevID <> t10.V018VmpTypePrevID and t1.V018VmpType = t10.V018VmpType and t1.DATEBEG < t10.DATEBEG order by DATEBEG) aa
where t1.DATEEND is null

drop table #V018
drop table #tmp

END