Untitled
unknown
plain_text
11 days ago
9.5 kB
3
Indexable
WITH relevant_threshold_investment_allowance_deltas AS (
SELECT DISTINCT
iadva.id AS iadva_id,
r.revtstmp AS revtstmp,
iadva.revtype AS revtype,
iadva.rev,
iadva.management_delta AS delta,
iadva.management_max_discount AS maxOrSpecialPrice,
iadva.is_management_delta_locked AS deltaLocked,
iadva.from_value AS fromValue,
iadva.threshold_type AS thresholdType,
iadva.group_investment_allowance_deltas_id AS giada_id,
LAG(iadva.management_delta) OVER (PARTITION BY iadva.id ORDER BY r.rev) AS deltaBefore,
LAG(iadva.management_max_discount) OVER (PARTITION BY iadva.id ORDER BY r.rev) AS maxOrSpecialPriceBefore,
LAG(iadva.is_management_delta_locked) OVER (PARTITION BY iadva.id ORDER BY r.rev) AS deltaLockedBefore,
LAG(iadva.from_value) OVER (PARTITION BY iadva.id ORDER BY r.rev) AS fromValueBefore,
LAG(iadva.threshold_type) OVER (PARTITION BY iadva.id ORDER BY r.rev) AS thresholdTypeBefore
FROM investment_allowance_delta_values_aud iadva
JOIN revinfo r ON iadva.rev = r.rev
-- WHERE iadva.rev = 25 AND iadva.group_investment_allowance_deltas_id = 20
),
investment_allowance_history AS (
-- Management Delta History
SELECT DISTINCT
iadva.id AS iadva_id,
r.revtstmp AS timestamp,
-- giada.modification_date AS modificationDate,
-- giada.modified_by AS modifiedBy,
-- giada.date_from AS dateFrom,
-- giada.date_to AS dateTo,
-- 'MANAGEMENT' AS roleType,
-- ps.name AS segmentName,
-- gda.group_discount_type AS discountType,
giada.rev AS giada_rev,
iadva.rev AS iadva_rev,
-- giada.id AS giada_id,
CASE
WHEN giada.group_investment_allowance_deltas_bundle_id IS NOT NULL THEN 'TIME_CONDITIONS'
ELSE 'BASIC_CONDITIONS'
END AS conditionType
,
COALESCE((
SELECT
COUNT(*) -
COALESCE((
SELECT COUNT(*)
FROM relevant_threshold_investment_allowance_deltas t
WHERE t.giada_id = giada.id
AND t.rev <= giada.rev
AND t.revtype = 2
), 0)
FROM relevant_threshold_investment_allowance_deltas t
WHERE t.giada_id = giada.id
AND t.rev <= giada.rev
AND t.revtype IN (0)
), 0) AS thresholdCount
,
-- LAG(COALESCE((
-- SELECT
-- COUNT(*) -
-- COALESCE((
-- SELECT COUNT(*)
-- FROM relevant_threshold_investment_allowance_deltas t
-- WHERE t.giada_id = giada.id
-- AND t.rev <= giada.rev
-- AND t.revtype = 2
-- ), 0)
-- FROM relevant_threshold_investment_allowance_deltas t
-- WHERE t.giada_id = giada.id
-- AND t.rev <= giada.rev
-- AND t.revtype IN (0)
-- ), 0)) OVER (PARTITION BY iadva.id ORDER BY r.revtstmp) AS thresholdCountBefore,
CASE
WHEN giada.id IS NULL THEN LAG(iadva.group_investment_allowance_deltas_id) OVER (PARTITION BY iadva.id ORDER BY r.rev)
ELSE giada.id
END AS giada_id
FROM investment_allowance_delta_values_aud iadva
JOIN revinfo r ON iadva.rev = r.rev
LEFT JOIN group_investment_allowance_deltas_aud giada
ON giada.id = iadva.group_investment_allowance_deltas_id
LEFT JOIN group_investment_allowance_deltas_bundles_aud giadba
ON giada.id = giadba.group_investment_allowance_deltas_id --> basic
OR giadba.id = giada.group_investment_allowance_deltas_bundle_id --> timely
LEFT JOIN price_segments ps
ON ps.id = giadba.price_segment_id
LEFT JOIN group_deltas_bundles gdb
ON giadba.group_deltas_bundle_id = gdb.id
LEFT JOIN groups g
ON gdb.id = g.group_deltas_bundle_id
LEFT JOIN group_discounts_aud gda
ON gda.id = g.discount_id
WHERE giada.rev = iadva.rev
OR giada.rev IS NULL --> to include delete records
OR (giada.rev != iadva.rev
AND giada.date_from != giada.date_to
AND iadva.rev = (SELECT MAX(iadva2.rev)
FROM investment_allowance_delta_values_aud iadva2
WHERE iadva2.group_investment_allowance_deltas_id = iadva.group_investment_allowance_deltas_id)
)
)
-- SELECT * FROM investment_allowance_history
SELECT
iah.timestamp,
-- iah.roleType,
-- iah.segmentName,
iah.conditionType,
-- iah.discountType,
-- iah.modifiedBy,
-- iah.modificationDate,
-- iah.dateFrom,
-- iah.dateTo,
-- iah.actionType,
iah.giada_rev,
iah.iadva_rev,
iah.giada_id,
-- iah.iadva_id,
iah.thresholdCount,
-- iah.thresholdCountBefore,
ARRAY_AGG(iah.iadva_id) AS iadva_id_list,
-- COALESCE((
-- SELECT
-- COUNT(*) -
-- COALESCE((
-- SELECT COUNT(*)
-- FROM relevant_threshold_investment_allowance_deltas t
-- WHERE t.giada_id = iah.giada_id
-- AND t.rev <= iah.giada_rev
-- AND t.revtype = 2
-- ), 0)
-- FROM relevant_threshold_investment_allowance_deltas t
-- WHERE t.giada_id = iah.giada_id
-- AND t.rev <= iah.giada_rev
-- AND t.revtype IN (0)
-- ), 0) AS thresholdCount
-- ,
LAG(COALESCE((
SELECT
COUNT(*) -
COALESCE((
SELECT COUNT(*)
FROM relevant_threshold_investment_allowance_deltas t
WHERE t.giada_id = iah.giada_id
AND t.rev <= iah.giada_rev
AND t.revtype = 2
), 0)
FROM relevant_threshold_investment_allowance_deltas t
WHERE t.giada_id = iah.giada_id
AND t.rev <= iah.giada_rev
AND t.revtype IN (0)
), 0)) OVER (PARTITION BY iah.giada_id ORDER BY iah.timestamp) AS thresholdCountBefore
,
COALESCE((
SELECT JSON_AGG(
JSON_BUILD_OBJECT(
'rev', t.rev, --> od usunięcia
'giada_id', t.giada_id, --> od usunięcia
'iadva_id', t.iadva_id, --> od usunięcia
'delta', t.delta,
'maxOrSpecialPrice', t.maxOrSpecialPrice,
'deltaLocked', t.deltaLocked,
'fromValue', t.fromValue,
'thresholdType', t.thresholdType,
'deltaBefore', t.deltaBefore,
'maxOrSpecialPriceBefore', t.maxOrSpecialPriceBefore,
'deltaLockedBefore', t.deltaLockedBefore,
'fromValueBefore', t.fromValueBefore,
'thresholdTypeBefore', t.thresholdTypeBefore
)
)
FROM relevant_threshold_investment_allowance_deltas t
WHERE t.giada_id = iah.giada_id
--> mozliwe ze jeszcze roleType match
AND t.rev = iah.giada_rev
), '[]') AS threshold_details
FROM investment_allowance_history iah
GROUP BY iah.timestamp, iah.conditionType, iah.giada_rev, iah.iadva_rev, iah.giada_id, iah.thresholdCount;Editor is loading...
Leave a Comment