select
mincome_installincome_recurringincome_install+income_recurring as income
from (
select
--install
DATE_TRUNC('month', dayscale.dt)::date as m
,sum(siv.price*siv.quantity*a.probability_perc/100
+ COALESCE(siv0.price*siv0.quantity*(100-a.probability_perc)/100, 0)
) as income_install
,0 as income_recurring
from
(SELECT dd::date as dt FROM generate_series (
'#DAYSCALE_START#'::timestamp
,'#DAYSCALE_FINISH#'::timestamp
, '1 day'::interval) dd) dayscale
join specification_item_version siv on (dayscale.dt=siv.dt_from)
join specification_item si on (siv.specification_item_uid=si.specification_item_uid AND si.pricing_model_id in (1))
join specification s on (si.specification_id=s.specification_id)
join agreement a on (s.contract_id=a.contract_id AND siv.agreement_version=a.agreement_version AND a.agreement_version=(select max(iiv.agreement_version) from specification_item_version iiv
join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid)
join specification isp on (ii.specification_id=isp.specification_id)
join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version)
where iiv.specification_item_uid=si.specification_item_uid
--AND ia.is_actual
))
join contract d on s.contract_id=d.contract_id
join contragent k on d.contragent_id=k.contragent_id
join svc on (si.svc_id=svc.svc_id)
left outer join agreement a0 on (s.contract_id=a0.contract_id /*AND siv.agreement_version=a0.agreement_version*/ AND a0.agreement_version=(select max(iiv.agreement_version) from specification_item_version iiv
join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid)
join specification isp on (ii.specification_id=isp.specification_id)
join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version)
where iiv.specification_item_uid=si.specification_item_uid
AND ia.agreement_version < a.agreement_version
AND ia.is_actual
))
left outer join specification_item_version siv0 on (siv0.specification_item_uid=si.specification_item_uid
AND siv0.agreement_version=a0.agreement_version)
where 1=1
group by DATE_TRUNC('month', dayscale.dt)
union all
select
DATE_TRUNC('month', dayscale.dt)::date as m
,0
/*,sum(siv.price*siv.quantity*a.probability_perc/100/dayscale.monthdays
+ COALESCE(siv0.price*siv0.quantity*(100-a.probability_perc)/100/dayscale.monthdays, 0)
)*/
,sum(
COALESCE(case when (siv.dt_to > dayscale.dt OR siv.dt_to IS NULL) then
siv.price*siv.quantity*(
case when a.is_actual then 100 else a.probability_perc end
)/100/dayscale.monthdays
else 0 /*по окончании рабочей сделки она дает нулевой вклад*/
end,0)
+
COALESCE(case when (siv0.dt_to > dayscale.dt OR siv0.dt_to IS NULL) then
siv0.price/dayscale.monthdays*siv0.quantity*(100- COALESCE(
case when a.is_actual then 100 else a.probability_perc end
,0))/100
else 0 /*по окончании базовой сделки она дает нулевой вклад*/
end,0)
)
from (SELECT dd::date as dt
, DATE_PART('days', DATE_TRUNC('month', dd) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) monthdays
FROM generate_series(
'#DAYSCALE_START#'::timestamp
,'#DAYSCALE_FINISH#'::timestamp
,'1 day'::interval) dd
) dayscale
left outer join specification_item_version siv on (dayscale.dt >= siv.dt_from /*AND (siv.dt_to > dayscale.dt OR siv.dt_to IS NULL)*/
AND siv.agreement_version=(select max(iiv.agreement_version) from specification_item_version iiv
join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid)
join specification isp on (ii.specification_id=isp.specification_id)
join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version)
where iiv.specification_item_uid=siv.specification_item_uid
AND (dayscale.dt >= iiv.dt_from /*AND (iiv.dt_to > dayscale.dt OR iiv.dt_to IS NULL)*/)))
/*AND ia.is_actual*/ /*свежее соглашение не обязано быть действующим*/
join specification_item si on (siv.specification_item_uid=si.specification_item_uid AND si.pricing_model_id in (2,3))
join specification s on (si.specification_id=s.specification_id)
join agreement a on (s.contract_id=a.contract_id AND a.agreement_version=siv.agreement_version)
join contract d on s.contract_id=d.contract_id
join contragent k on d.contragent_id=k.contragent_id
join svc on (si.svc_id=svc.svc_id)
-- находим предыдущее действующее соглашение
-- если свежее соглашение действует, то впр
-- нам нужно, чтобы в прошлом (до dt_from свежего соглашения) свежак был прозрачен,
-- а в будущем (после dt_to) факт перетирался свежим соглашением
left outer join agreement a0 on (s.contract_id=a0.contract_id AND a0.agreement_version=(
select max(iiv.agreement_version) from specification_item_version iiv
join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid)
join specification isp on (ii.specification_id=isp.specification_id)
join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version)
where iiv.specification_item_uid=si.specification_item_uid
AND ia.agreement_version < siv.agreement_version
AND ia.is_actual /*именно действующее соглашение, принимаемое за факт*/
)
)
left outer join specification_item_version siv0 on (siv0.specification_item_uid=si.specification_item_uid
AND siv0.agreement_version=a0.agreement_version AND (dayscale.dt >= siv0.dt_from /*AND (siv0.dt_to > dayscale.dt OR siv0.dt_to IS NULL)*/)
)
where 1=1
group by DATE_TRUNC('month', dayscale.dt)
) install_fix_payg
order by 1
select count(*) as cnt from specification_item where 1=1
Выручка по месяцам 2023-01-01 .. 2029-12-31#qRead.recordCount# записей
Месяцы без данных не показаны. Вариант с локальной рабочей версией строки (инстанса)
