-- create or replace view periods as

-- SELECT

-- distinct a.period as period

-- FROM

-- public.rtneo_contragent c,

-- public.rtneo_contract ct,

-- public.rtneo_contract_position cp,

-- public.rtneo_accrual a

-- WHERE

-- ct.contragent_id = c.id AND

-- cp.contract_id = ct.id AND

-- a.contract_position_id = cp.id AND

-- c.personal_account = 'ААА3812525543' and a.delete_ts isnull order by a.period;

select

p,

(select ct.number_

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.create_ts =

(select

max(ct.create_ts)

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.accepted = true

and ct.delete_ts is null

and p.period between ct.from_ and ct.before_)

) as ctn

from

periods p;

--

-- drop view periods;

*****************************************************************************************************************************************************

-- create TEMPORARY view periods as

with

periods as(

-- select a as per from generate_series('2019-01-01'::date, '2020-12-01'::date, '1 month') as a --Почему работает медленнее чем запрос? Нужно попробовать на больших объемах

SELECT

distinct a.period as per

FROM

public.rtneo_contragent c,

public.rtneo_contract ct,

public.rtneo_contract_position cp,

public.rtneo_accrual a

WHERE

ct.contragent_id = c.id AND

cp.contract_id = ct.id AND

a.contract_position_id = cp.id AND

c.personal_account = 'ААА3812525543'

and a.delete_ts isnull

order by a.period),

contracts as(

select

p.per as perct,

(select ct.id

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.create_ts =

(select

max(ct.create_ts)

from

public.rtneo_contract ct join

public.rtneo_contragent c on c.id = ct.contragent_id

where

c.personal_account = 'ААА3812525543'

and ct.accepted = true

and ct.delete_ts is null

and p.per between ct.from_ and ct.before_)

) as contract

from

periods p)

SELECT

a.period, a.document_number

FROM

contracts ct,

rtneo_accrual a left join

public.rtneo_contract_position cp

on a.contract_position_id = cp.id

where

a.period = ct.perct

and ct.contract = cp.contract_id

and a.delete_ts is null

order by a.period

--

-- drop view periods;