45 lines
2.5 KiB
SQL
45 lines
2.5 KiB
SQL
--ищем нетривиальные строки, где двигались даты в рамках подписанных соглашений (в базовых версиях)
|
|
with line as (SELECT *,
|
|
coalesce((LEAD(dt_from) OVER (PARTITION BY line_key ORDER BY dt_from, agreement_version,line_version)), (select max(dt) from elma.mv_dt)) dt_next
|
|
FROM
|
|
(
|
|
SELECT *,
|
|
MIN(dt_from) OVER (PARTITION BY line_key ORDER BY agreement_version desc,line_version desc) running_min_dt
|
|
FROM elma.mv_line where siv_is_actual AND probability_pc > 0
|
|
) t
|
|
WHERE t.dt_from=t.running_min_dt),
|
|
|
|
base_line as (SELECT *,
|
|
coalesce((LEAD(dt_from) OVER (PARTITION BY line_key ORDER BY dt_from, agreement_version,line_version)), (select max(dt) from elma.mv_dt)) dt_next,
|
|
max(agreement_version) OVER (PARTITION BY line_key) max_agr_ver
|
|
FROM
|
|
(
|
|
SELECT *,
|
|
MIN(dt_from) OVER (PARTITION BY line_key ORDER BY agreement_version desc,line_version desc) running_min_dt
|
|
FROM elma.mv_line where siv_is_actual AND deal_status_id IN ('5','6')
|
|
) t
|
|
WHERE t.dt_from=t.running_min_dt)
|
|
|
|
select contract, line_key, dt_from, agreement_version, line_version
|
|
from line
|
|
where line_key in (select line_key from line where deal_status_id in(5,6) group by line_key having count(distinct dt_from) > 1 )
|
|
--AND line_key='20240530T113112183'
|
|
order by contract, line_key, agreement_version, line_version
|
|
|
|
-- ВНИМАНИЕ! agreement_is_actual ставится только на последней версии соглашения, если поставить условием - никакой версионности не останется
|
|
--select * from elma.mv_line where line_key='14eeaaf0-c5f9-40f0-8949-944cfdb7ef13';
|
|
--select * from elma.deals_services where hash='14eeaaf0-c5f9-40f0-8949-944cfdb7ef13';
|
|
/*
|
|
SELECT *,
|
|
MIN(dt_from) OVER (PARTITION BY line_key, agreement_version ORDER BY agreement_version desc, line_version desc) running_min_dt
|
|
FROM elma.mv_line where siv_is_actual --AND agreement_is_actual --AND deal_status_id IN ('5','6')
|
|
and line_key='20240530T113112183'
|
|
order by agreement_version
|
|
*/
|
|
/*
|
|
select hash, count(*), count(distinct GREATEST(siv.date_nop,d."_plannedDueDate")) from elma.deals_services siv
|
|
left outer join elma.additional_agreements a on (a.__id = ANY(siv.additional_agreement))
|
|
left outer join elma.deals d on (d.__id = ANY(siv.deal))
|
|
where a.is_actual group by hash having count(distinct GREATEST(siv.date_nop,d."_plannedDueDate")) > 1
|
|
*/
|
|
--select * from elma. deals_services where hash='0ad3231a-93af-4eb0-81e0-fc678efb5419' |