1

I have a table that keeps records of account settings. When someone (administrator) makes a change to an account, a new entry gets recorded for that particular account at that time (updatetime). The changes in the record set are updatetime, operator and any settings that were changed (setting1, 2, 3 or any combination of them).

CREATE TABLE settingslog(
    updatetime DATETIME,
    account VARCHAR(20),
    administrator VARCHAR(20),
    setting1 VARCHAR(20),
    setting2 VARCHAR(50),
    setting3 VARCHAR(20)
);

INSERT INTO settingslog (updatetime, account, administrator, setting1, setting2, setting3) 
    VALUES
('2017-05-02 15:00:25', 'account1','admin2','enabled','enabled','enabled'),
('2017-05-02 14:00:13', 'account1','admin1','enabled','disabled','enabled'),
('2017-05-02 13:10:11', 'account1','admin1','enabled','enabled','enabled');

The desired output would be:

'2017-05-02 14:00:13', 'account1', 'admin1', 'setting2' changed from 'enabled' to 'disabled';
'2017-05-02 15:00:25', 'account1', 'admin2', 'setting2' changed from 'disabled' to 'enabled';

Thank you all in advance for your help! It is much appreciated.

  • I am asking because I dont know where to start:) – Max T May 4 '17 at 14:40
0

I added another line to your sample data:

+---------------------+----------+---------------+----------+----------+----------+
| updatetime          | account  | administrator | setting1 | setting2 | setting3 |
+---------------------+----------+---------------+----------+----------+----------+
| 2017-05-02 15:00:25 | account1 | admin2        | enabled  | enabled  | enabled  |
| 2017-05-02 14:00:13 | account1 | admin1        | enabled  | disabled | enabled  |
| 2017-05-02 13:10:11 | account1 | admin1        | enabled  | enabled  | enabled  |
| 2017-05-04 11:33:12 | account2 | admin3        | asdf     | asdf     | asdf     |
+---------------------+----------+---------------+----------+----------+----------+

Here's the query:

SELECT 
updatetime, account, administrator, s1, s2, s3
FROM (
    SELECT
    s.*
    , IF(@prevS1 != setting1, CONCAT('setting1 changed from ', @prevS1, ' to ', setting1), 'setting1 did not change') AS s1
    , IF(@prevS2 != setting2, CONCAT('setting2 changed from ', @prevS2, ' to ', setting2), 'setting2 did not change') AS s2
    , IF(@prevS3 != setting3, CONCAT('setting3 changed from ', @prevS3, ' to ', setting3), 'setting3 did not change') AS s3
    , @rn := (IF(@prevAccount != account, 1, @rn + 1)) AS grouprownumber
    , @prevAccount := account
    , @prevS1 := setting1
    , @prevS2 := setting2
    , @prevS3 := setting3
    FROM
    settingslog s 
    , (SELECT @prevAccount := NULL, @rn := 0, @prevS1 := NULL, @prevS2 := NULL, @prevS3 := NULL) var_init_subquery
    ORDER BY updatetime, account
) sq
WHERE grouprownumber > 1

and here the result:

+---------------------+----------+---------------+-------------------------+-------------------------------------------+-------------------------+
| updatetime          | account  | administrator | s1                      | s2                                        | s3                      |
+---------------------+----------+---------------+-------------------------+-------------------------------------------+-------------------------+
| 2017-05-02 14:00:13 | account1 | admin1        | setting1 did not change | setting2 changed from enabled to disabled | setting3 did not change |
| 2017-05-02 15:00:25 | account1 | admin2        | setting1 did not change | setting2 changed from disabled to enabled | setting3 did not change |
+---------------------+----------+---------------+-------------------------+-------------------------------------------+-------------------------+

To understand this, you better read this manual page about user defined variables.

And to explain it a bit:
The SELECT clause gets evaluated as is, so it's important that you don't change the order of the lines. And of course the ORDER BY clause is also important, as there is no order in a relational database, unless you specify it. So, when the table is read row by row, the IF() compares the value of the current row with the value of the variables. The variables are assigned the value of the current row after that. So when the next row is read and the IF() is evaluated again, the variables still hold the value of the previous row.

I also included a row number which resets, when another account is read (therefore it's also important, that you ORDER BY the account column). This is, so you can kick the entries in the outer query, where there's only one row for an account or of course because the first entry doesn't matter.

  • Thank you for taking the time to respond! Trying this solution. Will report back. – Max T May 4 '17 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.