select distinct org.GRBS_full_name, org.full_name,( case
when org.role_name ='КАЗЕННОЕ УЧРЕЖДЕНИЕ' then 'казенное учреждение'
when org.role_name ='БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ' then 'бюджетное учреждение' else null end) type_,
(
case
when ddd.yearn is not null then ddd.yearn
when ddd.yearn is null then ddd.yearc else null end) periodo
from DWH_ORGANIZATION_KGNTV org
join
(select contr.customerid,extract (year from contr.contractsigndate)yearc,extract (year from proc.publishdate) yearn
from dwh_contract_kgntv contr
LEFT join
(select MIN( DOCPUBLISHDATE) publishdate,PURCHASENUMBER,fullname
from DWH_START_NOTICE_NRPZ
GROUP BY PURCHASENUMBER,fullname) proc on contr.CONTRACTNOTICENUMBER=proc.PURCHASENUMBER)ddd on org.id=ddd.customerid
where org.role_name in ('казенное учреждение','бюджетное учреждение')
and ddd.yearn in (2015,2016,2017)
and ddd.yearc in (2015,2016,2017)
and org.FULL_NAME  ='САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ "ЦЕНТР ЭКСПЕРТНО-ТЕХНИЧЕСКОГО СОПРОВОЖДЕНИЯ"'
order by org.GRBS_full_name, org.full_name
--детализация
;

select contr.customerid,extract (year from contr.contractsigndate)yearc,extract (year from proc.publishdate) yearn
from dwh_contract_kgntv contr
join
(select MIN( DOCPUBLISHDATE) publishdate,PURCHASENUMBER,fullname
from DWH_START_NOTICE_NRPZ
WHERE fullname ='САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ "ЦЕНТР ЭКСПЕРТНО-ТЕХНИЧЕСКОГО СОПРОВОЖДЕНИЯ"'
GROUP BY PURCHASENUMBER,fullname) proc on contr.CONTRACTNOTICENUMBER=proc.PURCHASENUMBER;


select MIN( DOCPUBLISHDATE) publishdate,PURCHASENUMBER
from DWH_START_NOTICE_NRPZ
WHERE fullname ='САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ "ЦЕНТР ЭКСПЕРТНО-ТЕХНИЧЕСКОГО СОПРОВОЖДЕНИЯ"'
--like '%ЦЕНТР ЭКСПЕРТНО%'
GROUP BY PURCHASENUMBER
--извещения нет 2017
;

SELECT CONTRACTNOTICENUMBER,contractsigndate   FROM DWH_CONTRACT_KGNTV CONTR
WHERE CUSTOMERNAME = 'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ "ЦЕНТР ЭКСПЕРТНО-ТЕХНИЧЕСКОГО СОПРОВОЖДЕНИЯ"'
AND extract (year from contr.contractsigndate) = 2017;
--рни  из контрактов

SELECT * from dwh_start_notice where PURCHASENUMBER in (0372200154216000005,0172200002516000270,0372200154216000004)
-- нет таких извещений