hrwiki – nepatrolirane promjene po datumu

SQL

SET @from = "20231003";


SET @to   = DATE_FORMAT(@from + INTERVAL 1 DAY, "%Y%m%d");
SET @patrol_max   = DATE_FORMAT(@from + INTERVAL 35 DAY, "%Y%m%d"); /*patrolirati se može samo tridesetak dana od promjene*/

SELECT DISTINCT page_title, actor_name, rev_id, rev_timestamp, rev_actor FROM revision
JOIN actor ON actor_id = rev_actor 
JOIN page ON page_id = rev_page
LEFT JOIN user_groups ON ug_user = actor_user
/*LEFT JOIN change_tag ON ct_rev_id = rev_id*/

WHERE 
    /*actor_user is NOT NULL AND */
    rev_timestamp BETWEEN @from AND @to
AND ug_group is null
/*AND ct_tag_id NOT IN (958, 997) rmw-reverted=uklonjeno uređivanje;  */
AND rev_id NOT IN (SELECT ct_rev_id FROM change_tag WHERE ct_tag_id IN (59, 999) AND ct_rev_id=rev_id)
AND rev_id NOT IN (SELECT SUBSTRING(log_params FROM 26 FOR 7) FROM logging WHERE log_timestamp>@from AND log_timestamp <= @patrol_max AND log_action="patrol")
;

/*
SELECT page_title, actor_name, rev_id, rev_timestamp, rev_actor FROM revision
JOIN actor ON actor_id = rev_actor 
JOIN page ON page_id = rev_page
LEFT JOIN user_groups ON ug_user = actor_user
WHERE 
    rev_timestamp BETWEEN @from AND @to
AND ug_group is null
AND rev_id NOT IN (SELECT SUBSTRING(log_params FROM 26 FOR 7) FROM logging WHERE log_timestamp>@from AND log_timestamp <= @patrol_max AND log_action="patrol")
;
*/
SQL

Checking query status...
