25 lines
683 B
MySQL
25 lines
683 B
MySQL
|
-- AP1.8a: Portfolio-Priorität (relatives Ranking im Tenant)
|
||
|
|
|
||
|
|
ALTER TABLE initiatives
|
||
|
|
ADD COLUMN IF NOT EXISTS portfolio_rank INT;
|
||
|
|
|
||
|
|
WITH ranked AS (
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
ROW_NUMBER() OVER (
|
||
|
|
PARTITION BY tenant_id
|
||
|
|
ORDER BY updated_at DESC, title
|
||
|
|
) - 1 AS rn
|
||
|
|
FROM initiatives
|
||
|
|
)
|
||
|
|
UPDATE initiatives AS i
|
||
|
|
SET portfolio_rank = ranked.rn
|
||
|
|
FROM ranked
|
||
|
|
WHERE i.id = ranked.id AND i.portfolio_rank IS NULL;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_initiatives_tenant_portfolio_rank
|
||
|
|
ON initiatives (tenant_id, portfolio_rank);
|
||
|
|
|
||
|
|
COMMENT ON COLUMN initiatives.portfolio_rank IS
|
||
|
|
'Relatives Portfolio-Ranking (0 = höchste Aufmerksamkeit). AP1.8a';
|