Untitled
unknown
plain_text
9 days ago
8.8 kB
7
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
),
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,
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,
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,
CASE
WHEN giada.modified_by IS NULL
THEN LAG(giada.modified_by) OVER (PARTITION BY iadva.id ORDER BY r.rev)
ELSE giada.modified_by
END AS modifiedBy,
CASE
WHEN giada.modification_date IS NULL
THEN LAG(giada.modification_date) OVER (PARTITION BY iadva.id ORDER BY r.rev)
ELSE giada.modification_date
END AS modificationDate
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)
)
),
investment_allowance_history_final AS (
SELECT
iah.timestamp,
iah.roleType,
iah.segmentName,
iah.conditionType,
iah.discountType,
iah.modifiedBy,
iah.modificationDate,
iah.dateFrom,
iah.dateTo,
iah.giada_rev,
iah.iadva_rev,
iah.giada_id,
iah.thresholdCount,
ARRAY_AGG(iah.iadva_id) AS iadva_id_list,
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(
'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.roleType, iah.segmentName,
iah.conditionType,
iah.discountType,
iah.modifiedBy,
iah.modificationDate,
iah.dateFrom,
iah.dateTo,iah.timestamp, iah.conditionType, iah.giada_rev, iah.iadva_rev, iah.giada_id, iah.thresholdCount)
SELECT * FROM investment_allowance_history_final iahf
WHERE
roleType IN (:roleTypes)
-- Optional filter on modifiedBy (works only if we have at least one value in the list)
AND (:modifiedBy::TEXT[] IS NULL OR modifiedBy = ANY(:modifiedBy::TEXT[]))
-- Optional filter on modificationDate (works only if start and end dates are provided)
AND (modificationDate BETWEEN :startDate AND :endDate)
AND (
:deltaChange::BOOLEAN IS NULL OR :deltaChange = FALSE
OR EXISTS (
SELECT 1
FROM jsonb_array_elements(threshold_details::jsonb) elem
WHERE (elem->>'delta')::NUMERIC IS DISTINCT FROM (elem->>'deltaBefore')::NUMERIC
-- WHERE (elem->>'maxOrSpecialPrice')::NUMERIC IS DISTINCT FROM (elem->>'maxOrSpecialPriceBefore')::NUMERIC
)
)
ORDER BY timestamp DESC
LIMIT :limit OFFSET :offset;Editor is loading...
Leave a Comment