--БАЛАНСОВЫЕ ЗАПАСЫ!!!!!
WITH myCTE (MPV_COD, [MPV_name],  MPV_parent, MPV_NAME_PREDOK, Code_use_type, Code_mon_type, Code_district, Code_water_type, Adres) AS
	(
	/* выборка потомков и предков из таблицы ВГ*/
	SELECT t1.Code_MPV MPV_COD ,t1.[MPV_name] MPV_COD,  t2.Code_MPV MPV_parent, t2.[MPV_name] MPV_NAME_PREDOK, t1.Code_use_type Code_use_type, t1.Code_mon_type Code_mon_type, t1.Code_district Code_district, t1.Code_water_type Code_water_type, t1.Adres Adres
	FROM MPV_catalogue t1
	LEFT JOIN MPV_catalogue t2 ON t1.Code_parent = t2.Code_MPV
	)
SELECT 
	----Код МПВ

	
			myCTE.MPV_COD as 'Служебный',
	case when myCTE.Code_mon_type = '1'
	then myCTE.MPV_COD
	else myCTE.MPV_parent end as 'КОД МПВ',

	case when myCTE.Code_mon_type = '2'
--	then ' ****** ' 
	then myCTE.MPV_NAME_PREDOK
	else myCTE.MPV_name end as 'МПВ',
	
	case when myCTE.Code_mon_type = '2'
	then myCTE.MPV_COD
	else NULL end as 'КОД УМПВ',
	
	case when myCTE.Code_mon_type = '1'
	then ' ****** '
	else myCTE.MPV_name end as 'УМПВ',
	
	Voc_region.Region,
	myCTE.Adres,
	Voc_gg_structure.gg_structure,
	Voc_gidr.Gidrograf_ed,
	MPV_correspondence.Shir_grad, MPV_correspondence.Shir_min, MPV_correspondence.Shir_sec,
	MPV_correspondence.Dolg_grad, MPV_correspondence.Dolg_min, MPV_correspondence.Dolg_sec,
	--CAST(MPV_catalogue.DataExp AS date),
	convert(varchar, MPV_Spares_WG.Datauz ,104) as DataExp,
	Voc_inst.Inst,
	REPLACE(MPV_Spares_WG.nprot,'/','//') as nprot,
	Voc_water_gorizont.WG_index,
	Voc_water_gorizont.Water_gorizont,
--	[MPV_Spares_WG].Code_WG as 'Код ВГ на МПВ',

--группировка	[VZ_dob_god].Code_WG as 'Код ВГ на ВЗ',
	Voc_use_type.Use_type_abbr,
	REPLACE(AVG(MPV_Spares_WG.szapg),'.',',') as szapg,
	REPLACE(MPV_Spares_WG.zapA,'.',',') as zapA, REPLACE(MPV_Spares_WG.zapB,'.',',') as zapB, REPLACE(MPV_Spares_WG.zapC1,'.',',') as zapC1, REPLACE(MPV_Spares_WG.zapC2,'.',',') as zapC2,
--группировка	VZ_correspondence.Code_VZ,

	REPLACE(dbo.VZ_dob_god.Dob/1000,'.',',') as 'Dob/1000',
	--REPLACE(dbo.VZ_dob_god.Dob_god,'.',',') as Dob_god,
	цифры в запорсе неактуальны
--	REPLACE(AVG(dbo.VZ_dob_god.Dob_god),'.',',') as Dob_GOD,

	Voc_water_type.Water_type
	

from myCTE

left outer join MPV_correspondence
ON myCTE.MPV_COD = MPV_correspondence.Code_MPV

left outer join Voc_region
ON myCTE.Code_district = Voc_region.Code_region

left outer join Voc_gg_structure 
ON MPV_correspondence.Code_gg_structure = Voc_gg_structure.Code_gg_structure

left outer join	dbo.Voc_gidr 
ON MPV_correspondence.Code_gidr = Voc_gidr.Code_gidr

left outer join	dbo.MPV_Spares_WG 
ON myCTE.MPV_COD = MPV_Spares_WG.Code_MPV

left outer join Voc_inst 
ON MPV_Spares_WG.Code_inst = Voc_inst.Code_inst

left outer join	Voc_use_type 
ON MPV_Spares_WG.Code_use_type = Voc_use_type.Code_use_type

left outer join Voc_water_gorizont 
ON MPV_Spares_WG.Code_WG = Voc_water_gorizont.Code_WG

left outer join	dbo.VZ_correspondence 
ON myCTE.MPV_COD = dbo.VZ_correspondence.Code_MPV
	
left outer join	dbo.VZ_dob_god 
ON VZ_correspondence.Code_VZ = VZ_dob_god.Code_VZ AND VZ_dob_god.God = 2015

left outer join	dbo.Voc_water_type 
ON myCTE.Code_water_type = dbo.Voc_water_type.Code_water_type

--where MPV_Spares_WG.Datasz IS NULL AND MPV_Spares_WG.Code_inst IS NOT NULL
--AND MPV_Spares_WG.szapg IS NOT NULL


--ВАРИНАТ ИЗ ОБОЩАЮЩИХ ТАБЛИЦ
WHERE     (YEAR(dbo.MPV_Spares_WG.Datauz) <= 2015) AND (YEAR(dbo.MPV_Spares_WG.Datasz) > 2015 OR
                      YEAR(dbo.MPV_Spares_WG.Datasz) IS NULL) AND (dbo.f_get_count_parts(myCTE.MPV_COD) = 0) 

and MPV_Spares_WG.szapg <> 0


 
 GROUP BY
 	myCTE.MPV_COD,
	myCTE.Code_mon_type,
	myCTE.MPV_name,
	myCTE.MPV_parent,
	myCTE.MPV_NAME_PREDOK,
	Voc_region.Region,
	myCTE.Adres,
	Voc_gg_structure.gg_structure,
	Voc_gidr.Gidrograf_ed,
	MPV_correspondence.Shir_grad, MPV_correspondence.Shir_min, MPV_correspondence.Shir_sec,
	MPV_correspondence.Dolg_grad, MPV_correspondence.Dolg_min, MPV_correspondence.Dolg_sec,
	MPV_Spares_WG.Datauz,
	Voc_inst.Inst,
	MPV_Spares_WG.nprot,
	Voc_water_gorizont.WG_index,
	Voc_water_gorizont.Water_gorizont,
	[MPV_Spares_WG].Code_WG,

	Voc_use_type.Use_type_abbr,

	MPV_Spares_WG.zapA,
	MPV_Spares_WG.zapB,
	MPV_Spares_WG.zapC1,
	MPV_Spares_WG.zapC2,

	Voc_water_type.Water_type