32 lines
1.6 KiB
SQL
32 lines
1.6 KiB
SQL
with
|
|
component as (
|
|
select siv.__id as siv_uid, siv.discount, siv.cnt as quantity, siv.type as pricing_model_id
|
|
,siv.hash as line_key, siv.user_description, siv.is_actual as siv_is_actual
|
|
,siv.version, GREATEST(siv.date_nop,d."_plannedDueDate") as dt_from, siv.date_end as dt_to, siv.is_easy as is_simple, siv.cnt as quantity
|
|
, a.__id as agreement_uid, a.contract_uid, a.is_actual as agreement_is_actual, a.deal_uid
|
|
,d."_plannedDueDate", d.__name as deal
|
|
,p.code, p.price, p.cnt as param_quantity, p.discount as param_discount, p.name as sku
|
|
,p.price*p.cnt*(100-p.discount)/100 as cost
|
|
from elma.deals_services siv
|
|
join elma.additional_agreements a on (a.__id = ANY(siv.additional_agreement))
|
|
join elma.deals d on (d.__id = ANY(siv.deal))
|
|
join elma.service_parametrs p on (p.__id = ANY (siv.params))
|
|
join elma.aggrements c on (a.contract_uid = c.__id)
|
|
),
|
|
maxver as (
|
|
select max(version) as version, count(*) as cnt, line_key, contract_uid from component group by line_key, contract_uid
|
|
),
|
|
dayscale as (SELECT dd::date as dt
|
|
, DATE_PART('days', DATE_TRUNC('month', dd) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) monthdays
|
|
FROM generate_series(
|
|
'2023-01-01'::timestamp
|
|
,'2026-01-01'::timestamp
|
|
,'1 day'::interval) dd
|
|
)
|
|
select dayscale.dt, maxver.version, sum(c.cost), count(*) from
|
|
component c join dayscale on (dayscale.dt >= c.dt_from)
|
|
join maxver on (c.line_key=maxver.line_key AND c.version=maxver.version)
|
|
where --c.agreement_uid='0198183f-6dbc-73f7-914a-ce1347aea1a6'
|
|
c.contract_uid='0194a838-0351-7fea-b722-4289dfb4f240'
|
|
group by dayscale.dt, maxver.version
|
|
order by dayscale.dt |