select c.name, c.legalAddress from rtneo$Contragent c where (select count(contract) from rtneo$Contract contract where contract.contragent.id = c.id)=0 and (lower(c.legalAddress) like '%%г. байкальск%%' or lower(c.legalAddress) like '%%г байкальск%%') order by c.inn

select c.name, c.legalAddress, ct.number from rtneo$Contragent c join rtneo$Contract ct where c.id = ct.contragent.id and ct.accepted = true and ct.acceptanceDate = (select max(contract.acceptanceDate) from rtneo$Contract contract where contract.contragent.id = c.id) and (lower(c.legalAddress) like '%%г. байкальск%%' or lower(c.legalAddress) like '%%г байкальск%%') order by c.inn

lower(c.legalAddress) like '%%ангарск %%' or lower(c.legalAddress) like '%%ангарск'

lower(c.legalAddress) like '%%свирск%%'

lower(c.legalAddress) like '%%усолье%%' or lower(c.legalAddress) like '%%усольский%%'

lower(c.legalAddress) like '%%черемхово%%' or lower(c.legalAddress) like '%%черемховский%%'

lower(c.legalAddress) like '%%аларский%%'

Принятые основные договора

select c.name, c.legalAddress, ct.number from rtneo$Contragent c join rtneo$Contract ct where

c.id = ct.contragent.id

and not c.type = 3

and ct.accepted = true

and ct.acceptanceDate = (select min(contract.acceptanceDate) from rtneo$Contract contract where contract.contragent.id = c.id)

and (select count(contract) from rtneo$Contract contract where contract.contragent.id = c.id and contract.accepted = true)>1

and (lower(c.legalAddress) like '%%аларский%%') order by c.inn