9. Inspiration Articles 🏭

SELECT *
FROM proc."WorkLog" t1
WHERE EXISTS (
    SELECT 1
    FROM proc."WorkLog" t2
    WHERE t1."ElementId" = t2."ElementId"
      AND t1."StartDate" = t2."StartDate"
      AND t1."EndDate" <> t2."EndDate"
)
order by t1."StartDate", t1."Timestamp";

DO $$
DECLARE
    row_record proc."WorkLog"%ROWTYPE;
    minEndDate timestamp with time zone;
    rows_affected int := 0;
BEGIN
    FOR row_record IN
        SELECT *
        FROM proc."WorkLog" t1
        WHERE EXISTS (
            SELECT 1
            FROM proc."WorkLog" t2
            WHERE t1."ElementId" = t2."ElementId"
              AND t1."StartDate" = t2."StartDate"
              AND t1."EndDate" <> t2."EndDate"
        )
        order by t1."StartDate", t1."Timestamp"
    LOOP
        SELECT min("EndDate") INTO minEndDate
        FROM proc."WorkLog"
        WHERE "ElementId" = row_record."ElementId"
        AND "StartDate" = row_record."StartDate";

        UPDATE proc."WorkLog"
        SET "EndDate" = minEndDate
        WHERE "ElementId" = row_record."ElementId"
        AND "StartDate" = row_record."StartDate"
        AND "Timestamp" = row_record."Timestamp";
        rows_affected := rows_affected + 1;
    END LOOP;

    -- Output the total number of rows affected
    RAISE NOTICE 'Total rows affected: %', rows_affected;
END $$;