declare @mindate int
set @mindate=2012   

select Eo.FedRegion, Eo.Shortname,
	'Result'=case
	when 
	(SELECT top(1) id
	FROM dbo.PrimaryEduDocImportPackage
	WHERE dbo.PrimaryEduDocImportPackage.EduOrgId = EO.Id
	and  PrimaryEduDocImportPackage.StatusId not in (6,7,3,5,101) and PrimaryEduDocImportPackage.IssueYear = @mindate) is null 
	then 0--'Не предоставлены', 

	when 
	(SELECT top(1) id
	FROM dbo.PrimaryEduDocImportPackage  
	WHERE dbo.PrimaryEduDocImportPackage.EduOrgId = EO.Id
	and  PrimaryEduDocImportPackage.StatusId in (3,5,101) and PrimaryEduDocImportPackage.StatusId not in (6,7)  and PrimaryEduDocImportPackage.IssueYear = @mindate) is not null 
	then 1--'Предоставлены, но требуется повторная отправка данных (выявлены ошибки при обработке)'
	when 
	(SELECT top(1) id
	FROM dbo.PrimaryEduDocImportPackage
	WHERE dbo.PrimaryEduDocImportPackage.EduOrgId = EO.Id
	and  PrimaryEduDocImportPackage.StatusId in (6,7) and PrimaryEduDocImportPackage.IssueYear = @mindate) is not null 
	then 2--'Предоставлены'
	else 0
	end,
	'someyear'=case
	when ((select top(1) id
	from dbo.EduOrgYearStatus
	where dbo.EduOrgYearStatus.EduOrgId=EO.Id and dbo.EduOrgYearStatus.[Year] = @mindate
	and EduOrgYearStatus.EduOrgActive=1 and EduOrgYearStatus.EduOrgHasAccred=1 and EduOrgYearStatus.EduOrgIssuesDocs=0
	and EduOrgYearStatus.EduOrgIssuesStateDocs=0 and EduOrgYearStatus.EduOrgLicensed=1) is not null)
	or
	((select top(1) id
	from dbo.EduOrgYearStatus
	where dbo.EduOrgYearStatus.EduOrgId=EO.Id and dbo.EduOrgYearStatus.[Year] = @mindate
	and EduOrgYearStatus.EduOrgActive=1 and EduOrgYearStatus.EduOrgHasAccred=0 and EduOrgYearStatus.EduOrgIssuesDocs=0
	and EduOrgYearStatus.EduOrgIssuesStateDocs=0 and EduOrgYearStatus.EduOrgLicensed=1) is not null)
	or
	((select top(1) id
	from dbo.EduOrgYearStatus
	where dbo.EduOrgYearStatus.EduOrgId=EO.Id and dbo.EduOrgYearStatus.[Year] = @mindate
	and EduOrgYearStatus.EduOrgActive=1 and EduOrgYearStatus.EduOrgHasAccred=1 and EduOrgYearStatus.EduOrgIssuesDocs=0
	and EduOrgYearStatus.EduOrgIssuesStateDocs=0 and EduOrgYearStatus.EduOrgLicensed=1) is not null)	
	or
	((select top(1) id
	from dbo.EduOrgYearStatus
	where dbo.EduOrgYearStatus.EduOrgId=EO.Id and dbo.EduOrgYearStatus.[Year] = @mindate
	and EduOrgYearStatus.EduOrgActive=1 and EduOrgYearStatus.EduOrgHasAccred=0 and EduOrgYearStatus.EduOrgIssuesDocs=0
	and EduOrgYearStatus.EduOrgIssuesStateDocs=0 and EduOrgYearStatus.EduOrgLicensed=0) is not null)
	or 
	((select top(1) id
	from dbo.EduOrgYearStatus
	where dbo.EduOrgYearStatus.EduOrgId=EO.Id and dbo.EduOrgYearStatus.[Year] = @mindate
	and EduOrgYearStatus.EduOrgActive=2 and EduOrgYearStatus.EduOrgHasAccred=0 and EduOrgYearStatus.EduOrgIssuesDocs=0
	and EduOrgYearStatus.EduOrgIssuesStateDocs=0 and EduOrgYearStatus.EduOrgLicensed=0) is not null)
	then 0
	else 1
	end
	from EducationOrganization EO inner join dbo.EduOrgForm OPF on EO.OrgFormId = OPF.Id
--	left join dbo.PrimaryEduDocImportPackage on EO.id=dbo.PrimaryEduDocImportPackage.EduOrgId
	where 
	
	EO.LeadingEduOrgId is null 
	and EO.IsActive = 1
	and EO.Id <> '5F6A52CE-060E-47DA-84E5-23DD412EFFCA'